Compute the total elapsed working time

[rrlarson]

I have searched high and low for a formula that would compute the total elapsed working time (in minutes) of an event taking into consideration only the working days and specified working hours. I finally broke down and wrote one myself. Here it is ... enjoy!

Named Ranges:

• DayStart - time of day business starts
  DayEnd - time of day business ends
  HolidayList - vector of holiday dates
  StartEvent - the date/time of the start of the event
  EndEvent - the date/time of the end of the event

Formula:

Please Login or Register  to view this content.

How it works?
Although it appears a little complicated, it basically is:
The number of minutes in the first day of the event (as long as it is a working day)
PLUS
The number of minutes of the last day of the event (also provided it is a working day)
PLUS
Number working days between the first day and last day * working minutes in a day

[daddylonglegs]

Hello rrlarson,

Is that meant to work whatever the start/end dates/times? It doesn't seem to work if the end date is a Saturday for example 09:00 on a Thursday to 09:00 2 days later should give a result of 1020 (480 minutes on Thursday and the whole of Friday, i.e. 540 minutes) .....but your formula gives a result of just 480.

The standard version I use should work OK for any times (assuming EndEvent >= StartEvent) - here it is converted to use your names

Please Login or Register  to view this content.

[JBeaucaire]

Moved to a thread of it's own, as per forum rules.

RRLarson - When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)


@DDL - shame on you... Almost gave you a for-fun infraction for that.
Good thing your so awesome.