Results 1 to 5 of 5

Thread: Formula Not Working - Finding a Date that Falls within a Date Range

  1. #1

    Unhappy Formula Not Working - Finding a Date that Falls within a Date Range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I'm fairly new to Excel, but I can manage and am a fast learner. Recently I was put to the task of creating a spreadsheet that could calculate overtime and include a column which would only add the hours that were equal to or fell between a date range. I've figured out how to calculate the overtime... I'm just having trouble returning the value from the total hours for a particular day, if it falls within a date range.

    See attached spreadsheet...

    Cell H6 contains the formula I am having trouble with.

    =IF(AND(B6 falls between the value of B3 or value of D3),G6,0)

    B3 = Start of Payroll
    D3 = End of Payroll
    B6 = Current Date
    G6 = Total of Hours
    H6 = Total of Hours that fall between date range (B3,D3)

    What am I doing wrong? 2Timesheet with OT Calculation.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    The date entered in B3 as 12/01/2014 is not a date according to Excel. You need to enter it as 12/1/2014. You can then format it to show up as 12/01/2014 (but you can't type it in that way).


  3. #3
    Quote Originally Posted by NBVC View Post
    The date entered in B3 as 12/01/2014 is not a date according to Excel. You need to enter it as 12/1/2014. You can then format it to show up as 12/01/2014 (but you can't type it in that way).
    YAY!! Such a little thing and it had a big effect on my results... guess I better pay more attention next time.

    Thank you soooo much!!

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,313
    Articles
    0
    Excel Version
    2010 on Xubuntu
    FYI Dates are numbers and when you type them they will right align. Text will always automatically left align in the cell ( if you don't change that manually of course)

  5. #5
    Quote Originally Posted by Pecoflyer View Post
    FYI Dates are numbers and when you type them they will right align. Text will always automatically left align in the cell ( if you don't change that manually of course)
    Thank you... now I see why it didn't work, I added an additional zero it didn't need to the date "code" (since they are numbers, not actual dates).
    I'm so used to typing dates in "mm/dd/yyyy" format for work... force of habit.

    Thanks you both for your help and advise! I'll be coming to this forum often...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •