Date time Macro or Formula.

[olly69]

I would like a formula or macro that uses dates and times.

It needs to caclute the differences in times.
e.g 9:00 15/10/02, 10:00 16/10/02 answere is 8 hours.

The problem is i would like the calculation to ignore weekends and hours outside of a 9 - 5 shift at work.

 

[eliW]

On 2002-10-15 06:50, olly69 wrote:
I would like a formula or macro that uses dates and times.

It needs to caclute the differences in times.
e.g 9:00 15/10/02, 10:00 16/10/02 answere is 8 hours.

The problem is i would like the calculation to ignore weekends and hours outside of a 9 - 5 shift at work.

Hi olly69 and welcome to th board.
Can you explain how did you get result of 8 hours when starting shift at 15/10 09:00 and ending at 16/10 10:00 ??

Eli

 

[olly69]

Sorry have not explained that too well.

I need the time taken to complete the work. So he started the work at 9.00 on the 15/10 and finished it at 10.00 16/10. He only works a 9-5 day so it took him 9 hrs to complete 8 hrs from 9-5 on 15/10 and 1hr on on 16/10 to finish the work so i want the answere to be 9 hrs.

The formula must also ignore Sat and Sun as not working days, so if 15/10 fell on Friday and he completed it on the Monday would be the hours taken to complete the work between 9-5 on Friday and Monday excluding Sat and Sun.

Hope this is clearer and u can help

 

[Iridium]

Check out the Excel helpfile on NETWORKDAYS and Microsoft Knowledge Base Article - Q259200 at http://support.microsoft.com/ - that should get you started at least

Chris

 

[eliW]

On 2002-10-15 07:23, olly69 wrote:
Sorry have not explained that too well.

I need the time taken to complete the work. So he started the work at 9.00 on the 15/10 and finished it at 10.00 16/10. He only works a 9-5 day so it took him 9 hrs to complete 8 hrs from 9-5 on 15/10 and 1hr on on 16/10 to finish the work so i want the answere to be 9 hrs.

The formula must also ignore Sat and Sun as not working days, so if 15/10 fell on Friday and he completed it on the Monday would be the hours taken to complete the work between 9-5 on Friday and Monday excluding Sat and Sun.

Hope this is clearer and u can help

Look at this post:
http://216.92.17.166/board/viewtopic.php?topic=10203&forum=2
Eli