Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Calculate working days but change working week

Hi

I have different timestamps that I need to calculate the time between them
however I need to exclude evenings and weekends.

I'm using the standard NETWORKDAY function which works great for those
countries who work Monday Friday.

However I need to also calculate the same for those countries working Sun
Thurs and Sat Wed

The formula that I have is:

=(NETWORKDAYS(J36,K36)-2)*10/24+((WEEKDAY(J36,2)<6)*(MAX(0,TIME(17,0,0)-MOD(J36,1))))-((WEEKDAY(J36,2)<6)*(MAX(0,TIME(7,0,0)-MOD(J36,1))))+((WEEKDAY(K36,2)<6)*(MAX(0,MOD(K36,1 )-TIME(7,0,0))))-((WEEKDAY(K36,2)<6)*(MAX(0,MOD(K36,1)-TIME(17,0,0))))

i.e.:
J36 = 12/08/2008 09:00
K36 = 15/08/2008 10:00

TAT = 31:00

This stops the clock at 17.00 and starts it at 7.00 which I still need, I
just need to say that the working week is different.

i.e. if the 13th & 14th are a weekend the calculation would be 11.00 hours
but I can not figure out how to change the formula.

I've looked at this too long now, any ideas?

Thanks
Sam
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,420
Default Calculate working days but change working week

=(NETWORKDAYS(J36+I36,K36+I36)-2)*10/24
+((WEEKDAY(J36+I36,2)<6)*(MAX(0,TIME(17,0,0)-MOD(J36+I36,1))))-((WEEKDAY(J36+I36,2)<6)*(MAX(0,TIME(7,0,0)-MOD(J36+I36,1))))
+((WEEKDAY(K36-I36,2)<6)*(MAX(0,MOD(K36-I36,1)-TIME(7,0,0))))-((WEEKDAY(K36-I36,2)<6)*(MAX(0,MOD(K36-I36,1)-TIME(17,0,0))))

where I36 holds

0 - Mon-Fri
1 - Sun-Thu
2 - Sat-Wed

--
__________________________________
HTH

Bob

"SamB" wrote in message
...
Hi

I have different timestamps that I need to calculate the time between them
however I need to exclude evenings and weekends.

I'm using the standard NETWORKDAY function which works great for those
countries who work Monday Friday.

However I need to also calculate the same for those countries working Sun

Thurs and Sat Wed

The formula that I have is:

=(NETWORKDAYS(J36,K36)-2)*10/24+((WEEKDAY(J36,2)<6)*(MAX(0,TIME(17,0,0)-MOD(J36,1))))-((WEEKDAY(J36,2)<6)*(MAX(0,TIME(7,0,0)-MOD(J36,1))))+((WEEKDAY(K36,2)<6)*(MAX(0,MOD(K36,1 )-TIME(7,0,0))))-((WEEKDAY(K36,2)<6)*(MAX(0,MOD(K36,1)-TIME(17,0,0))))

i.e.:
J36 = 12/08/2008 09:00
K36 = 15/08/2008 10:00

TAT = 31:00

This stops the clock at 17.00 and starts it at 7.00 which I still need, I
just need to say that the working week is different.

i.e. if the 13th & 14th are a weekend the calculation would be 11.00 hours
but I can not figure out how to change the formula.

I've looked at this too long now, any ideas?

Thanks
Sam



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Making weekend days working days - the system cuts the working tim Fluffy Excel Discussion (Misc queries) 1 May 30th 08 10:02 PM
5 working days of a week deepika :excel help[_2_] Excel Discussion (Misc queries) 3 January 31st 08 09:37 PM
Count working days by week inta251 Excel Worksheet Functions 7 January 18th 07 09:01 AM
Calculate elapsed working days RUSH2CROCHET Excel Discussion (Misc queries) 6 March 9th 06 09:36 PM
calculate number of working days philc Excel Worksheet Functions 2 June 1st 05 07:48 AM


All times are GMT +1. The time now is 04:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"