Adding working hours

[sreerag446]

Hi All,

I want to write a macro to add business hours to a datetime value, excluding weekends, non-working hours and holidays.

e.g., I have a value (datetime) of "18/12/2009 11:30:00 AM", I want to add 10 hours to this, so as to get the result as "19/12/2009 12:30:00 PM".

Please help.

[royUK]

Adding 10 hours will not give that time, it would be 21:30

=+D8+((1/24)*10)

[sreerag446]

The answer in the example is by considering 8:00 to 17:00 as working hours.

[royUK]

The answer in the example is by considering 8:00 to 17:00 as working hours.

I can't see how you add 10 hours to come up with that time. The result you have is 25 hours ahead

[teylyn]

Hi,
try this:

with your date/time value in A2 and the business/working hours you want to add as a time value in B2

=DATE(YEAR(A2),MONTH(A2),DAY(A2))+TIME(8,0,0)+(B2-(TIME(17,0,0)-MOD(A2,1)))

make sure you enter the time value as hours in B2, i.e. 10:00

hth

[DonkeyOte]

I think it will prove a little more complex than that given the requirement to use working days only... (ie WORKDAY but with Time constraint)

Personally, I'd recommend a search of WORKDAY and daddylonglegs... none of us will be able to match whatever he has created previously ... finding the appropriate formula might take a bit of searching though.

http://www.excelforum.com/excel-gene...-to-dates.html

EDIT: on that basis I suspect output for the given example should in fact be 21st rather than 19th ...

[DonkeyOte]

This is daddylonglegs' formula:

Please Login or Register  to view this content.

where D2 is 08:00 and E2 is 17:00 ... A1 is start datetime and B1 is time to add... format result as date/time.

Using your example the above would return 21/12/2009 12:30

NOTES:
-- Pre XL 2007 WORKDAY requires activation of Analysis ToolPak Add-In
-- WORKDAY has optional parameter for public holiday range - see XL Help for more info.

[sreerag446]

Thanks a lot everyone for replying...

DonkeyOte, you soln is working excelent for me

[param.aluna]

Hi I wanted the formula to calcuate the difference in hh:mm:ss format between two cells with following conditions
A1=11/Sep/2013 02:40:58 PM
B1=18/Sep/2013 03:02:53 PM
C1= HH:MM:SS format

I need formaulas for calculatingfor the following conditions.
1. Need to calculate the time difference for business hours Mon to Fri for business hours 09 to 19 excluding weekends.Means for 5 days a week
2. Need to calculate the time difference between hh:mm:ss for 12 by 6
3. Need to calculate the time difference for 24 hours.

Thanks in advance.