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

1. ## Formula Not Working - Finding a Date that Falls within a Date Range

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.

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. 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. Originally Posted by NBVC
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. 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. Originally Posted by Pecoflyer
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
•