Exact Number of Weekend & Weekday Days (or hours) between Two Dates

[jaek99]

I'm looking to calculate the number of weekdays and weekend days between two dates that include a time stamp.

I've used this formula:
NETWORKDAYS(I8,J8)-1+MOD(J8,1)-MOD(I8,1)

And then subtracted from the total time difference (date/time start - date/time end) to get at the weekend days. However, the networkdays only works when the start date is not a weekend day.

Any thoughts? Thanks

 

[T. Valko]

I'm looking to calculate the number of weekdays and weekend days between two dates that include a time stamp.

I've used this formula:
NETWORKDAYS(I8,J8)-1+MOD(J8,1)-MOD(I8,1)

And then subtracted from the total time difference (date/time start - date/time end) to get at the weekend days. However, the networkdays only works when the start date is not a weekend day.

Any thoughts? Thanks

How about posting a few examples and telling us what results you expect.

 

[jaek99]

Start Time:
2/3/2011 12:00:00 PM (Thursday)

End Time:
2/10/2011 8:00:00 AM (Thursday)

Using my method mentioned in my first post, I return correctly 116 Weekday Hours and 48 Weekend Hours.


The following example doesn't work:

Start Time:
8/14/2010 9:30:00 AM (Saturday)

End Time:
8/16/2010 8:00:00 AM (Monday)

The networkday formula returns a negative value

 

[jaek99]

deleting this thread, starting a new one as what i'm looknig for is slightly more complicated

 

[barry houdini]

Hello jaek99, welcome to MrExcel, did you get any further with this?

This formula should give you the weekday hours, even if the start date/ end date is at the weekend

=NETWORKDAYS(I8,J8)+NETWORKDAYS(J8,J8)*(MOD(J8,1)-1)-NETWORKDAYS(I8,I8)*MOD(I8,1)

format result cell as [h]:mm