Counting working days.

[GEM]

A1=2/13/2009 10:00
A2=2/14/2009 10:00
A3=A2-A1

I get an answer of "1 day 00:00" which means 1 day 0 hours 00 minutes. This
is on a 24 hour a day basis. How can I get an answer on an 8 hour a day
basis??

 

[Chip Pearson]

What would be the correct answer for your example data? It is subject
to interpretation. At the simplest, the formula would be

=(B1-A1)*24/8
=3

which indicate that there are 3 8-hour period between the datetime
values, but somehow I don't think that is what you are looking for.

Provide more detail.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[GEM]

Thanks for the reply Chip, let me give you an example...

A1=2/13/2009 9:00
A2=2/15/2009 18:00

In 24 hour a day basis, this would give you the answer of 2 days 9:00. On an
8 hour a day basis the answer I'm looking for is 7 days 1:00.

How can I get to this answer??

Would you also know if it is possible to do calculations only on days from
Monday through Friday?? For example last Friday 13th and today Monday the
16th,

A1=2/13/2009 23:58
A2=2/16/2009 00:04
A3=0 days 0:06

Is this possible??

Thanks!!

 

[Bob Phillips]

=INT((A2-A1)/8*24)&" days "&TEXT(MOD((A2-A1)*24,8)/24,"h:mm")

 

[GEM]

Thanks Bob, but this function would mean that my result is in text format
which would make doing math operations impossible. I got the result I wanted,
but how can I format this result into a cell which can be added, substracted,
etc.

 

[Bob Phillips]

You can't because 57 hours is not 7 days 1 hour, so you need to work with
the real hours and convert it at very point.

 

[Scott Parsley]

How about this...

A1=2/13/2009 9:00
A2=2/15/2009 18:00
A3=(DAY(A2-A1)*24+HOUR(A2-A1)) // Number of hours between dates = 57
A4=QUOTIENT(A3,8) // Number of 8 hour days =
7
A5=MOD(A3,8) // Number of
remaining hours = 1

Then you can combine A4 and A5 as you like to use in later calcuations?

The approach for not counting weekends is a bit more complicated...

A1=2/13/2009 23:58
A2=2/16/2009 00:04
A3=((NETWORKDAYS(A1,A2)-2)*24)+((24-HOUR(A1))-(MINUTE(A1))/60)+((HOUR(A2))+MINUTE(A2)/60)

Will result in A3 containing 0.10, which represents 0.10 of an hour, or 6
mins. I'll leave it for you to experiment with what results this gives you
for other times and how you can then use a similar approach to that above to
split it out into days, hours, mins depending on how you want to use the
values later.

Cheers,
Scott