Email:      Pass:    Pass?

Subscribe for Free Excel tips & more!
E-mail:
Advertisements

# How Do I Calculate Difference In Days & Hours Between Two Dates E.

How do I calculate difference in days & hours between two dates excluding
weekends and public holidays. Using excel 2002

## Similar Excel Tutorials

Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Calculate the Difference Between Two Times in Excel
Here, youll learn how to get the difference between two times in Excel. A common example of this is for when someo ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...

## Helpful Excel Macros

Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da

## Similar Topics

Can anyone help with this issue?

I'm trying to calculate the # of hours between the two dates, excluding holidays and weekends, with the standard work day equal to 8am to 5pm.

See the attached file for example. The highlighted cells should logically show 1.5 hours, but I always show 9 hours using network days.

Any help is appreciated.

Hi,

I am trying to write an excel formula that will calculate the time difference (hours and minutes) between two dates (the time a ticket was opened and the time it was closed), taking into consideration only working hours (8:30 am - 5 pm) and excluding weekends and holidays.
For example:
Ticket opened on : Friday Mar 4 2011 4:00 pm
Ticket closed on : Tuesday Mar 8 2011 9:00 pm
Holiday ex: Mon Mar 7 2011

Then the answer should be: 1.5 hours

I am aware of NETWORKDAYS formula which handles weekends, any help is appreciated in adding to the formula to count for business hours and exclude holiday days (I suppose the holidays are to be stored seperately in the sheet).

Thank you.
Ray

Hi Friends,

Please help me on the excel formula to calculate the difference between two dates shown as Days:Hours:Minutes Format. The difference should exclude the weekdends i.e., Saturdays and Sundays. I am looking for formulas in the two criteria.
Kindly help using the below sample data.

Criteria 1:

Start Date: 1/25/2007 9:28:00 PM
End Date: 1/26/2008 8:46:00 AM

Answer should be showing the difference in Days:Hours:Minutes excluding weekends.
I calculated the difference using regular Minus formula i.e., End Date - Start Date.
I got the result as 365.471 days. Then I multipled 0.471 with 24, the result is 11.304 Hours. Finally I multipled 0.304 with 60, the result is 18 Minutes. Result is in bits and pieces to be retyped as 365 days, 11 Hours and 18 Minutes i.e., 365:11:18. Still the above result is not meeting the requirement, it is not excluding weekends (Saturdays and Sundays). Any help in this regard.

Criteria 2:

Start Date: 1/25/2007 9:28:00 PM
End Date: 1/26/2008 8:46:00 AM

Office working hours: 9.30 AM to 6.3 PM.

Answer should be the difference between above dates in Days:Hours:Minutes excluding weeknds and time if the enquiry is raised other thn office hours.

I am working in an ITES industry. Customer raises an enquiry i.e, start date and time.
We close the enquiry i.e, end date and time. The difference is what is known as TAT.
There are chances that either the customer can raise a ticket on a weenend and also after the office business hours as it is raised online. Now customer claims that we are taking more time i.e., out TAT is more. I am a six sigma guy and has been given this project. Before I jump in,I want to define the problem well and state this much is the actual TAT excluding weekends and considering our office hours only. Here is where I am stucked at.
For criteria1 and criteria2 what are the formulas and how to calculate. I have taken 400 enquires to study and want to calculte for all 400. Hence looking for formulas. Please help me friends.

With regards,
venu_creative

Can anyone perhaps help with this issue?

I am trying to calculate the difference between 2 dates where 1day = 6hrs, excluding holidays and weekends, e.g. If the start date and end date are the same day the formula needs to return the value of 6.

I have used the formula =(B2-A2)*6+6, but this doesn't exclude weekends and holidays so isn't 100% accurate, see attached my example.

Any help would be highly appreciated.

Hi,

I have the following table as attached. I need a formula to calculate the difference in dates based on the following criteria:

a) Exclude Saturday
b) Exclude Public Holidays
c) If 6 Sept 2010 is the start date and 8 Sept 2010 is end date, then the difference should be 2 days instead of 3 days.

In the example as attached, the difference between 6 Sept 2010 and 13 Sept 2010 should be 4 days after excluding Saturdays and Public Holidays.

Appreciate help.

I have a cross post he

http://www.mrexcel.com/forum/showthr...17#post2454117

Hi there
I would like to know how i can calculate the difference (in days) between two dates, excluding the weekends from the calculation.
For example lets say A1 has 22 May 2008 and A2 has 27 May 2008. How do i calculate the difference between A2 and A1 so i get the answer as 3. Using Networkdates gives me 4 (it includes the start date in the calculation). I can't use datedif because sometimes my first date is smaller than the second date, in which case it gives me an error.
Also, occasionally my two dates are from two different years like it can be 22 May 2007 and 27 May 2008.
It would be really helpful if someone can help with this.
Many Thanks in advance
Cheers
Nitin

I would like to calculate the number of hours between 2 dates/times excluding weekends & bank holidays.

For example 27/03/2009 9:00:00 to 30/03/2009 10:00:00 = 25 hours
Or 09/04/2009 9:00:00 to 14/04/2009 10:00:00 = 25 hours (as includes bank holidays)

Is there a formula to do this? I would have a range of cells that lists the bank holidays.

Greetings all,

Please assist if your can.

I require a formula which will assist me in calculating the difference between two dates:

I have two dates in cell A4 and B4 and need to calculate the difference between the two dates - the answer should tell me - number of days, number of hours, number of minutes.

I do have some advannced excel knowledge but have been battling for days - PLEASE HELP

Thanking you in advance

Regards,
Joseph

I need to calculate the number of working hours between two dates and display the result as the number of hours and minutes in an excel sheet. This in itself is not a problem, but I want to use only office hours in the function, i.e., ignore weekends and do not calculate hours after 6:00 and before 19:00, public holidays excluded etc.

I tried using the networkdays formula, example:

A1: 8/6/07
A2: 8/7/07
A3: =networkdays(A1,A2,holidays)
Answer: 2

Yet it counts each date as one day and actually need the difference between the two, i.e. 1 day.

How can I get the actual difference even it was a positive number of days or a negative number of days?

Hi
I'm trying to calculate the difference between two dates and times and returns the answer in days hours mins for workdays only. I've seen similar threads but nothing seems to be quite right or I can't get to work

E2 contains date/time call raised in custom format dd/mm/yyyy hh:mm
M2 contains date/time action taken in custom format dd/mm/yyy hh:mm
on another worksheet I have a list called "holidays" that will have all the public holidays

I basically want AA2 to show M2-E2 in days & hh:mm only or even just hh:mm for working days only assuming a standard 5 day week with standard working hours (I have not specified working days or working hours anywhere). Also what format should cell AA2 be set up as?

Oh and if M2 isn't filled in yet I need the cell AA2 to remain blank.

Help would be gratefully appreciated
Tammy

I am having some trouble calculating a time difference between 2 times. I have started by using two columns that hold dates and i have a simple formula to calculate the difference in days between each date, this is fine. I know need 2 count the difference in hours between eg 10 Jul 09 @ 23:00hrs and 12 Jul 09 @ 13:30hrs. I am currently using the following formula but it doesnt count the hours if the time difference if its over two days:

Code:

```=U3-H3 +IF(H3>U3,1)
```

Can anyone help?

Maybe this can only be done with VBA? If anyone has an idea how to handle this please post reply. Thanks much!.........

I am trying to figure out how to calculate the time difference (not including weekends and holidays) between two dates and times. I want the time difference ideally to be shown in minutes. If this is not possible then in hours with a resolution of at least 1/10th (i.e. 42.3 hrs) will be fine. The difficult part to this is I do not want the weekends and holidays included in the calculation. I know about the Networkdays function but haven't been able to get the result to be in minutes (or to 1/10th hrs) that I need. Here is an example of my data and manual calculation of the result I am looking for:

Start Date/Time: 12/28/04 1:30 AM

End Date/Time: 1/4/05 1:45 PM

Holidays: 12/29/04, 12/30/04

Manual time difference calculation: 3 Days (not including weekend & holidays) + 12 hrs + 15 min = 4320 min + 720 min + 15 min = 5055 minutes (or 84.3 hrs)

Best regards,
Dan
__________________________________________

I am trying to calculate difference between two dates including weekends. But I need it an actual number e.g 4 days not 04/00/00 which I have been getting with some formulas

I have tried the date difference formula but I get the message VALUE in the cell.

Can anyone help please?

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also how can I exclude holidays if there are any.

I want to calculate the difference between two date and time stamps excluding weekends and holidays. The result should be in hours:Minutes:Seconds. Below is an example..

Reported Date+ Last Resolved Datetime Holiday List 12/18/09 12:07 PM 2/17/10 6:53 PM 1-Jan-10 15-Feb-10 16-Feb-10

Hi all
This is my second post for the same query. I wanted to calculate the difference between dates, excluding the weekends. Please see my thread 'Calculate difference between dates excluding weekends'. After much discussion, daddylonglegs suggested a brilliant formula =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer.
This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.
Many many thanks in advance
Cheers
Nitin

Hi all
I wanted to calculate the difference between dates, excluding the weekends. After much thought i used =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer.
This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.
Many many thanks in advance
Cheers
Nitin

I have 2 fields on access, start date and an end date. I want to calculate the total number of days between the above dates without including weekends.
These dates are chosen using a calendar on a form.

Please help!

Hi All,

I have two different days in two different coloumns in the same worksheet and I want to put the difference between these two dates in an adjacent coloumn in the following format:-

Stat Date

Stop Date

Difference

Formar Reguired

4/1/07 10:20

4/3/08 5:50

367.8125

# Days # Hours # Minutes

I need the Difference in the above mentioned break up in days, hours and mins.

Can anyone help?

Thanks in advance.
Sandan

Hi!

I need to calculate the time difference in hours between two dates.

For example:
2010-05-03 15:50
2010-05-10 14:05

I want my output to be in hours (eg. 166.8 hours or whatever is the correct value for those dates).

Is there a formula that can do this calculation for me in excel? I have managed to get the rounded number of days, but not hours. Other things I have tried have only given me an error message.

Thanks very much!

lbergman

How should I calculate working days, hours and minutes between two dates?I am creating a 'System Outage Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days, hours and minutes are between the two date/time stamps to determine lost production hours given the ff.:

Variables:
- 'Start time' (A1)
- 'Time resolved' (B1)
- 'Duration' (C1)

Constants:
Work Days = Monday to Friday
Work Hrs = 6:30 AM to 3:30 PM (1 hour 30 minute lunch break)
Public Holidays = (AA1:AA30)

Formats:
Start time: dd/mm/yyyy hh:mm AM/PM
Time resolved: dd/mm/yyyy hh:mm AM/PM
Duration: d "Days" hh:mm

Any help would be greatly appreciated!

MLEGASPI

Dear Forum,

I am picking a cue from the recent post on Time DIfference Between 2 Dates..

http://www.excelforum.com/excel-work...-weekends.html

Difference in time HOURS/MINS betwn 2 dates excluding Lunch Tea Breaks, Holidays,Leaves,WkOffs but including Overtime

It was a sheer coincidence that I was looking for something similar for calculating the time in Hours or Mins (Billable Hours) for people involved in data entry work.

Just some things additional are as follows:

Lunch & Tea Breaks and Overtime over and above the the regular shift-timing..

The Start Date, Closing Date and Start Time and Closing Time as well as Overtime Rates is a new addition...

Regards
e4excel

Hi All,

Was wondering if someone was able to help me with a little problem that i'm having. I am trying to find the difference between two dates and times but i need to minus the weekends.

My Spreadsheet currently looks like like this:

B3 has 15/11/2010 8:56:00 AM
C3 has 22/11/2010 11:34:00 AM
D3 has =(INT(C3)-INT(B3))+MAX(MOD(C3,1)-MAX(MOD(B3,1))) Which = 7.109722
E3 has =INT(TEXT(D3,"[h]")/24)&" days"&TEXT(MOD(D3,1),""" and"" h ""hours and ""mm"" minutes""") Which = 7 Days and 2 Hours and 38 Minutes

What i'm looking to do is have a list of all the weekends for the month in my spreadsheet and if the dates go through the weekend they will be subtracted from the total days. So my example for the example should be: 5 Days and 2 Hours and 38 Minutes.

Any help would be great

Thanks in Advanced

Hi everybody

Saying that I'm a bit stuck would be an understatement, sooooo I would realy apreciate if someone could help me out on this one.

I need to calculate the time it take a staff member to do a task problem is that a task may take several days to complete, soooooo calculating the time should'nt be a problem or so I thought.

Until I noticed that my simple sum formula was working so well that it was even including weekends holidays and non working hours, BRILLIANT!!!

If anyone could assist me with removing the weekends, holidays and non working hours I would realy appreciate it.

Im currently ussing Excel 2010.

D4=Time Started (Format yyyy/mm/dd hh:mm)
E4=Time End (Format yyyy/mm/dd hh:mm)
F4=Answer (Format [h]:mm)

Working hours are from 07:00 to 17:00

If there is a way to avoid VBA it would be great as I have no idea how to use it. If not I will have to learn how to use it very quickly.

Thank you