Date/time formula frustration

[manic_psyche]

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

 

[Scott Huish]

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]

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)