# Date time Macro or Formula.

#### olly69

##### New Member
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### eliW

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

Replies
2
Views
123
Replies
3
Views
54
Replies
5
Views
65
Replies
4
Views
174
Replies
1
Views
73

1,181,299
Messages
5,929,166
Members
436,653
Latest member
Dieguete

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back