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

Rosy3392

New member
Joined
Dec 9, 2014
Messages
3
Reaction score
0
Points
0
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? View attachment 2Timesheet with OT Calculation.xlsx
 
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).
 
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!! :thumb:
 
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)
 
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...:yo:
 
Back
Top