Date and Time Formula additions.

[Ramair]

G’Day

I have used a posting for time formats:-
Name Description Example
StartDT The starting date and time 5-Aug-2010 13:00
EndDT The ending date and time 6-Aug-2010 15:00
DayStart The time of day that the normal work day
begins. 08:00
DayEnd The time of day that the normal work day ends. 16:30
HolidayList A range containing a list of dates to excluded, e.g., holidays or vacation days. H1:H10
To return the total number of working hours, use the following formula:
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

In addition to the above I have two more variables I need to take into account:-

Name= Fridays Day End, Description =Every Friday Finish Early 14:30hrs
Name=FRED Part time, Description=Only Works Tuesday to Thursday normal hrs – 08:00-16:30 hrs 3 days per week.
Therefore Fridays normal time is 08:00-14:30hrs (as opposed to 08:00-16:30)
One worker doesn't work Mondays or Fridays.
If someone would be so kind to include the above in the formula posted above I would be very appreciative.
I thought maybe it needed a look up table…but I got confused due to the length of the formula!!

Thanks in anticipation for any help.

Regards
Stuart

[teylyn]

Hello Stuart,

Welcome to the forum. Do you think you could put this in a workbook and post that, along with your expected results mocked up? This would be soooo much easier than trying to recreate your file.

cheers