Calculating elapsed hours, respecting work hours and weekends

**Jath** · 06-13-2011, 12:59 PM

Hello,

I am trying to create an excel formula that appropriately calculates the elapsed time from when a ticket was created to when it has been closed out. This is also respecting work hours from 8AM to 5PM, so a 9 hour day, and also respecting weekends.

So, take

OPENED
4/20/11 7:53 PM
.
CLOSED
4/26/11 2:55 PM

Now the opened time is at 7:53PM, so since this is outside of work hours would become 4/21/11 8:00AM to respect the start of work hours. Along with that, it was opened for 5 days after moving it to 8:00AM, so the hours elapsed was 51 hours and 55 minutes, or 51.9 hours in decimal format (which is what I want).

This calculation would be converted into days, also, for different priorities, so hopefully it's possible to calculate the days based on business hours too. So, for an elapsed time of 18 hours, it took 2 days since business hours are 9 hours a day.

Thanks for the help.

Work hours are Monday to Friday, 8AM to 5PM - 9 hours.

Edit: This is also for Excel 2010. For some reason it says 03 up at the top right.

**shg** · 06-13-2011, 01:19 PM

Formula compliments of Daddylonglegs

Please Login or Register  to view this content.

dateBeg and dateEnd are dates and times (4/20/11 7:53 PM and 4/26/11 2:55 PM in your example)

timeBeg and timeEnd are pure times (9AM and 5PM in your example)

This is also for Excel 2010. For some reason it says 03 up at the top right.

User CP > Edit your details

**Calzone** · 07-08-2011, 11:03 AM

This thread is similar to an issue I am trying to resolve.

I am trying to write a formula, not a macro, to calculate the following:

I need to calculate the elapsed time between two dates and times, while ignoring weekends and US federal holidays. The work hours are Mon thru Fri from 8:00am to 5:00pm. I have a lookup table set up with federal holidays for the next five years.

For instance:

Task start: 6/30/11 3:30pm

Task end: 7/5/11 10:00am

Holiday: 7/4/11

Formula should return work hours and minutes between these dates, ignoring period between 5:00pm and 8:00am each work day and ignoring 7/2/11, 7/3/11, and 7/4/11 = 12 hours, 30 minutes work time to complete the sample task

I would appreciate any help offered concerning this task. Thanks.

**shg** · 07-08-2011, 11:10 AM

Welcome to the forum, Calzone.

That is a formula, not a macro.

Please take a few minutes to read the forum rules, and then start your own thread if you have a further question.

Thanks.

**Calzone** · 07-08-2011, 11:25 AM

Thank you for the feedback.

**jm_rodriguez666** · 07-24-2012, 06:09 PM

Hi shg,

regarding Daddylonglegs' formula =(NETWORKDAYS(dateBeg, dateEnd)-1)*(timeEnd-timeBeg)+IF(NETWORKDAYS(dateEnd, dateEnd),MEDIAN(MOD(dateEnd,1), timeEnd, timeBeg),timeEnd)-MEDIAN(NETWORKDAYS(dateBeg,dateBeg)*MOD(dateBeg,1), timeEnd, timeBeg), what does 1 indicate in the formula?

**Cutter** · 07-24-2012, 07:32 PM

Hello jm_rodriguez666, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**shg** · 07-25-2012, 09:14 AM

NETWORKDAYS(dateBeg, dateEnd)-1 counts the number of whole workdays between the date, prior to consideration for additional, perhaps partial days.

MOD(date&time, 1), strips the date and returns just the time; it's equivalent to date&time - int(date&time)