Date to determine if cell to be filled or not

[Tarma]

Hallo all,

I'm working on a sheet for overtime and need the Weekdays to populate under the 1.5 hours column and the Sundays and public holidays to populate in the 2 hours column.

Date inserted, I used a simple IF statement to test =IF(B5="Sunday",G5=F5,"")
B5 - where date is inserted
G5 - where data and formula is inserted
F5 - the value I want returned (hours worked)

I set up another sheet with public holidays and all the days of the year and tried a VLOOKUP, but the return I need is in the hours worked and not the date range table.

I also tried the highlighting public holidays and Sundays but that works well with the set up of a calendar.

Please help me, my light bulb is broken...

 

[Special-K99]

This very confusing.

1) This

=IF(B5="Sunday",G5=F5,"")

will return TRUE FALSE or blank.

2) "B5 - where date is inserted"
B5 will NEVER = Sunday since "Sunday" is not a date. It's a day of the week.

You probably need IF(WEEKDAY(B5)=1...)

3) "G5 - where data and formula is inserted"
Cells can contain data OR formulas. Not both.

Based on all this you'd be better of pasting a small image of the file.

 

[Tarma]

I deleted the G5=F5 part and just inserted F5, and I see what the issue is. B5 has a formula that produces the work Sunday so the formula doe snot recognize it unless I type the work Sunday. But I need the formula, so I'm back to the drawing board.

Can you provide an alternative formula to use?

I can't select a picture, I have broken the sheet down hopefully it helps:
B - Day of the week (converted from column C)
C - date worked
D - time in
E - time out
F - total hours worked (time out minus time in)
G - 1.5 hours
H - 2 hours