• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding hours to date

M

mediatx

Member
Hi everyone, I searched, tried and failed, a hundred times, several hours now...

Am I that blind?


I have a service schedule from (C2)09:00 - (D2)17:00

A taskplan starting with a Startdate in A9 entered as 29.04.2010 09:00:00

Cell A10 needs to be the Enddate, calculated based on a value (Duration) entered based on a A11 in [h]:mm format (02:30:00)


The result in A10 has to be Networkdays, meaning that adding 9 hours will result in A10 should be 30.04.2010 10:00:00


Can someone please be so kind and help me on this?
 
Mediatx

In A9 try entering the date/time as 29/04/10 09:00:00 instead of 29.04.10 09:00:00

You can still use a DD.MMM.YY HH:MM.SS display format

The Format used to display it doesn't mean that is how it is entered


In A10 =A9+(D2-C2)


Using the format you have used it will be entered as text and then not work
 
Hi mediatx,


I think this is similar to what you are trying to work out?


http://www.excelforum.com/excel-worksheet-functions/593345-time-date-tracking-only-during-business-hours.html
 
@Huy: I am located in europegermany and thats our date format, does Excel require the format you mention always or is that more a US thing?
 
@oldchippy

Just went through it, and it looks right at the first place but actually its the enddate and time I want to calculate and I dont get networkdate to work without the enddate provided in the formula. Is my mind still blocked?
 
Mediatx

I can't comment on your date format


If you enter a date in say A1 and enter =A1+1 in A2 is it displaying the correct date 1 day after the first.

If it does the date format is correct, otherwise change it and see what happens
 
@Hui, thanks for the update. The date format is OK and working as expeceted.

Leaves the question open on how to add hours to a date and calculating the new date/time in other cell considereing working hours. :(
 
If you have C2 as 09:00 and D2 as 17:00

put =D2-C2 somewhere it should show 08:00

now convert that to "," format and it should show 0.33

which is 8/24 hrs or 0.33 days

If thats correct then

In A10 =A9+(D2-C2)

will give you the right answer we just need to get the format correct


check the Number Format for A10 (Ctrl 1, Number, Custom)

try

d/mm/yyyy h:mm or

d.mm.yyyy h:mm


If that doesn't work can you put an example on the net somewhere ?
 
Hi mediatx,


Am I right in thinking that you are saying for example, if you work on a Friday between 9:00 to 17:00, which is 8hrs but you have a task taking 9hrs, then the finishing date should be the Monday at 10:00AM?
 
@oldchippy, exactly thats what I need, hoewever, not only for weekends, but worktime in general (public holidays is not that relevant).


So if I have office time from 09:00 to 17:00 and have a task on Monday starting on 16:00 lasting 2 hours, it means, it finishes on the next workday (tuesday in this case= at 10:00


However, I only have the startday and time and the duration of the task and need to calculate enddayte and time accordingly to the sample i just gave.


Any chance to do that? For me its simply magic (which doesnt work so far)
 
Mediatx


Coming up in 2010 is a new or extended version of Networkdays called NETWORKDAYS.INTL

It uses the format

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

where weekends can be customised to any day, pair or more of days and holidays will accept a range of date serial numbers as Holidays


I don't think it will help you with this problem though.


Have you got access to MS Project, this s exactly what it is good at doing
 
Hi mediatx,


I think I have a solution!


Going back to your original question:-


I have a service schedule from (C2)09:00 - (D2)17:00

A taskplan starting with a Startdate in A9 entered as 29.04.2010 09:00:00

Cell A10 needs to be the Enddate, calculated based on a value (Duration) entered based on a A11 in [h]:mm format (02:30:00) <<<<<< Should this be 09:00

The result in A10 has to be Networkdays, meaning that adding 9 hours will result in A10 should be 30.04.2010 10:00:00


Follow these steps

1. In E2 put this =D2-C2 format as hh:mm

2. A9 has 29.04.2010 09:00:00 formatted as ddd dd.mm.yyyy hh:mm

3. A11 has 09:00 format as [hh]:mm

4. A10 has the following enormous formula to account for weekends

=IF(WEEKDAY(INT(A9)+INT(MOD(A9,INT(A9))-C2+A11/E2)+C2+MOD(A9,INT(A9))-C2+A11-(INT(MOD(A9,INT(A9))-C2+A11/E2)*E2))=7,INT(A9)+INT(MOD(A9,INT(A9))-C2+A11/E2)+C2+MOD(A9,INT(A9))-C2+A11-(INT(MOD(A9,INT(A9))-C2+A11/E2)*E2)+2,IF(WEEKDAY(INT(A9)+INT(MOD(A9,INT(A9))-C2+A11/E2)+C2+MOD(A9,INT(A9))-C2+A11-(INT(MOD(A9,INT(A9))-C2+A11/E2)*E2))=1,INT(A9)+INT(MOD(A9,INT(A9))-C2+A11/E2)+C2+MOD(A9,INT(A9))-C2+A11-(INT(MOD(A9,INT(A9))-C2+A11/E2)*E2)+1,INT(A9)+INT(MOD(A9,INT(A9))-C2+A11/E2)+C2+MOD(A9,INT(A9))-C2+A11-(INT(MOD(A9,INT(A9))-C2+A11/E2)*E2)))


Format it the same as A9 and I believe this will give you the result you want.
 
Hey Chandoo this is magic and I even dont understand the formula, however, it works for weekends but does not consider the service schedule on weekdays. Adding 9 hours in A11 results in same endday at 18:00 - did I do something wrong?


Please notice that I might be too much a newby and use some wrong terms.

A11 has the duration in hours and minutes, I dont know the format, but when entering 2:15 that means 2 hours, 15 minutes for me :) - I know Excel sometimes thinks very different :)
 
You must log in or register to reply here.
Back
Top