# Date/time formula frustration

#### manic_psyche

##### New Member
I've been using a formula to give me the total number of working hours between to dates... I found it on this site http://www.cpearson.com/excel/DateTimeWS.htm

But now I need something like the WORKDAY formula, but one which will calculate the time as well as the date. Where the result of the formula is a Date & time value.

Does anyone know of such a formula, or has the brain power to solve to come up with one? Any help would be appreciated.

Cheers
Manic_Psyche

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Scott Huish

##### MrExcel MVP
Hi, welcome to the board!

It sounds like you want to add a number of days to your original date/time, which to me sounds like you would end up at the same exact time as the original but a different day.

Assuming original date/time in A1 (adding 15 days in this example):

=WORKDAY(A1,15)+MOD(A1,1)

#### barry houdini

##### MrExcel MVP
Assuming your start date & Time in one cell - A2 - and that this day will be a workday - and time will be within your business hours (i.e. between DayStart and DayEnd)

DayStart in B2 (e.g 08:00)
DayEnd in C2 (e.g. 17:00)
hours to add in D2 (in time format)
Holiday list is G1:G10

then use the following formula, with result cell formatted appropriately e.g. mmm-d-yyyy hh:mm

=WORKDAY(A2,(D2-B2+MOD(A2,1))/(C2-B2),G\$1:G\$10)+B2+MOD(D2-B2+MOD(A2,1),C2-B2)

Replies
0
Views
132
Replies
17
Views
342
Replies
2
Views
71
Replies
20
Views
650
Replies
3
Views
205

Threads
1,195,582
Messages
6,010,577
Members
441,557
Latest member
Jbest23

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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