Time and date calculations

[phillipcook]

hi all.

I am having a little trouble working out a couple of formulas. What I have is the following:

column A: DATE
column B: START TIME
column C: FINISH TIME
column D: PUBLIC HOLIDAY DATES

what I need to calculate is the following 5 different formulas:
1 formula
If the date is between Monday to Friday and is not a public holiday, the amount of hours and minutes worked outside of 6am to 6pm.

2 formula
If the date is between Monday to Friday and is not a public holiday, the amount of hours and minutes worked between 6am and 6pm.

3 formula
if the date is a Saturday

4 formula
if the date is a Sunday

5 formula
if the date is a public holiday (public holiday dates are listed in a column D for the year.

Thanks heaps in advance guys

 

[stumac]

I am pretty sure there are other/better ways of calculating these, but here is my effort:

1 -  =IF(NETWORKDAYS(A2,A2,D2:D15)>0,(C2-B2)-(MIN(18/24,C2)-MAX(6/24,B2)),0)
2 -  =IF(NETWORKDAYS(A2,A2,D2:D15)>0,MIN(18/24,C2)-MAX(6/24,B2),0)
3 -  =WEEKDAY(A2,16)=1
4 -  =WEEKDAY(A2,16)=2
5 -  =ISNUMBER(MATCH(A2,D2:D15))

This assumes that the actual dates(i.e.not the header) for public holidays starts in D2 and goes to D15 - - if there are more public holidays than that then you would need to amend.

 

[stumac]

5 should be -

=ISNUMBER(MATCH(A2,D2:D15[COLOR=#ff0000],0[/COLOR]))

Also for 1 & 2 I would format the cells using the custom format [HH]:mm

 

[phillipcook]

Awesome mate!!!

I have done them and they seem to work brilliantly, however I made one mistake. With formula 3, I need it to also exclude public holidays. Not sure how to do this.

With the last 3 formulas I have managed to make them show a blank if there is no start date, but I can’t seem to figure out how to make the first two display blanks if their is no start date. Could you please help me out with this one?

thank you for all the help!!!

 

[phillipcook]

Sorry I made a mistake. Only formula 5 I can make it show up as a blank if the start time is missing. Would you please help me make the other 4 formulas show a blank result if the start time is missing?
Thanks heaps!!

 

[stumac]

I you still want one and two to show a zero if the start date isnt blank but the number of hours is zero then:

1  -   =IF(A2="","",IF(NETWORKDAYS(A2,A2,D2:D15)>0,(C2-B2)-(MIN(18/24,C2)-MAX(6/24,B2)),0))
2  -   =IF(A2="","",IF(NETWORKDAYS(A2,A2,D2:D15)>0,MIN(18/24,C2)-MAX(6/24,B2),0))

And for three - is it likely a public holiday is on a Saturday?

You can check that the date doesn't appear on the list of public holidays:

=AND(WEEKDAY(A2,16)=1,ISERROR(MATCH(A2,D2:D15,0)))