Work days difference between 2 dates.

[Jeremy]

Please help me. I need to find the time elapsed for workdays only between a
column of dates and times (available in one column or two) and a fixed date
and time. I only need hours of a work day. I have gotten so far as to get
the whole number of workdays elapsed, but I need more precise data than that.


03373

 

[ryguy7272]

I think this will help you:
=(NETWORKDAYS(A1,B1)-2)*8/24+((WEEKDAY(A1,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A1,1))))-((WEEKDAY(A1,2)<6)*(MAX(0,TIME(8,0,0)-MOD(A1,1))))+((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(8,0,0))))-((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(17,0,0))))

I saw it on a post recently. The idea is quite brilliant; I certainly
didn't come up with the concept...

Anyway, hope it helps you out,
Ryan--

 

[Jeremy]

Wow. That certainly did something. I am a true novice with excel. What I
would like to know is how did this calculate the difference between times if
the only cell I referenced was the date cell? I don;t really understand what
the formula did, but I changed it to meet the cells of the sheet that I have
and it certainly gave me numbers.

:

I think this will help you
=(NETWORKDAYS(A1,B1)-2)*8/24+((WEEKDAY(A1,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A1,1))))-((WEEKDAY(A1,2)<6)*(MAX(0,TIME(8,0,0)-MOD(A1,1))))+((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(8,0,0))))-((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(17,0,0))))

I saw it on a post recently. The idea is quite brilliant; I certainly
didn't come up with the concept...

Anyway, hope it helps you out,
Ryan--



RyGuy

 

[Bob Phillips]

This formula assumes an 8 hour working day, between 8:00AM and 5PM with an
hour break. But it also caters for days that start after 8:00AM and/or
finish before 5PM, and adds in those hours. I think this is one of my
concoctions, I certainly recall a similar one for an 8.5 hour day, but
looking at this, I think it may be wrong in not handling the break.

What exactly are your criteria?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jeremy]

ok, I should clarify a little bit. I run a report that tells me outstanding
orders. Every order that we receive should be processed within 24 hours,
excluding weekends. I would like to exclude a list of holidays as well, if
possible to make the report more accurate. I do not need to assume a 8 hour
work day. The report gathers a list of created dates\times and order
numbers. I will be putting in a fixed date\time in one cell and need to know
the difference in hours (based on a 24 hour work day, I guess) between the
two dates. I can get the date and time in either one cell or two, it pulls
from SAP so I can have that configured any way I want. Let me know if you
need anything else.

 

[Bernd P]

Hello,

Then I would suggest to use NETWORKDAYS.

Regards,
Bernd

 

[Jeremy]

I tried that and only got the whole number of days difference. I need more
deatiled data than that. The data that I have is the date and time the order
was created and I need to calculate the amount of hours elapsed between that
date\time and and a given date.

 

[ryguy7272]

Bob, that was YOUR brainchild! I remember now. In it's original incarnation
it was set up to deal with 8.5-hour-days.