Calculating Date and time with networkdays function

[djacob69]

Ok. Here is the issue I am having.

I need to calculate the date and time difference between and start and end date /time of a project. The results needs to be in hours and minutes.

Example

Start Date(a1) Start Time(B1) End Date(c1) End Time(d1) = Result(e1)

09/14/2009 14:00 09/14/2010 14:00

The formula I currently use in E1 is =c1+d1-a1+b1

I have E1 formatted as [h]"h":mm"m"

How do i calculate the results so that only workdays are used? I know I should be able to use the NETWORKDAYS function, but seemed to be having issues with it within a formula....

Any help would be great appreciated.

Dave

 

[JoeMo]

Ok. Here is the issue I am having.

I need to calculate the date and time difference between and start and end date /time of a project. The results needs to be in hours and minutes.

Example

Start Date(a1) Start Time(B1) End Date(c1) End Time(d1) = Result(e1)

09/14/2009 14:00 09/14/2010 14:00

The formula I currently use in E1 is =c1+d1-a1+b1

I have E1 formatted as [h]"h":mm"m"

How do i calculate the results so that only workdays are used? I know I should be able to use the NETWORKDAYS function, but seemed to be having issues with it within a formula....

Any help would be great appreciated.

Dave

NETWORKDAYS returns an integer number of days, even if the start and end times for those dates differ. Multiplying this by 24 will give you the number of hours between the start and end dates, but will not comprehend any differences in partial days that arise because the start time on the start date differs from the end time on the end date. Look into the DATEDIF function for help. (Google on it - won't be found in Excel's Help).

 

[djacob69]

Now I am really confused....Do you have an example of what you mean?

 

[barry houdini]

Are you counting all hours on working days (24) or do you only want to consider the working day, e.g. 9 to 5?

In general, assuming your start and end times/dates are always with working hours you can use this formula to count all hours on working days

=NETWORKDAYS(A1,C1)-1+D1-B1

and format as before

If you only want hours betweem 09:00 and 17:00 for example, you can change to this version

=(NETWORKDAYS(A1,C1)-1)*("17:00"-"9:00")+D1-B1

 

[djacob69]

This is great. Just what I needed.....

Thank you very much..!

 

[Sangeetha]

I have a similar problem.
Data: Start date and time in col F, end date and time in col L, lead time (hh:mm:ss) in col M. Lead time calculation formula:=IF(ISBLANK(L2),(NETWORKDAYS(F2,NOW(),Holidays)-1+MOD(NOW(),1)-MOD(F2,1)),(NETWORKDAYS(F2,L2,Holidays)-1+MOD(L2,1)-MOD(F2,1)))
Lead time is calculated as soon as the start date is entered, considering current time (NOW) as end date until actual end date and time are entered.
2nd October is in holiday list.

Issue: For rows with start date as 2nd october, lead time does not give the correct result.
E.g. start date and time = 02-10-2014 13:56:00 and End date and time = 07-10-2014 12:03.
Result should be 60:03:00 but I get 46:07:00

I guess this is because, though 2nd october is considered holiday, start time is calculated from 13:56 and not as 3rd october 00:00

Hope I have clearly laid the case. Any help appreciated.

 

[barry houdini]

Your formula only works if start and end date are within working hours, this version should work for any start/end time/date

=NETWORKDAYS(F2,IF(L2="",NOW(),L2),Holidays)+NETWORKDAYS(IF(L2="",NOW(),L2),IF(L2="",NOW(),L2),Holidays)*(MOD(IF(L2="",NOW(),L2),1)-1)-NETWORKDAYS(F2,F2,Holidays)*MOD(F2,1)

 

[Sangeetha]

There is no business hours in specific. In working days 24 hours is business hours and holidays and weekends to be ignored while calculating leadtime.

 

[Sangeetha]

It worked! Thanks a ton!