Solved How to calculate minutes between two dates/times

May 14, 2013 at 01:04:46
Specs: Windows XP, Excel 2010
Can anyone help with a formula that will calculate the number of minutes between two dates and times. I have a received date and time and an action date and time.
I only want to include the time calculated based on a workday Monday to Friday 8.30 to 5.00pm.
That is exclude Sat/Sun and not include the time from 5.00pm to 8.30am
ie received 1/May/13 2:00pm actioned 6/May/13 9:00am would return 20 hours 30 minutes (1230 minutes)

Hopefully I have provided enough detail I am a bit of a novice.
I have it working for Friday through to Monday with a long formula but doing something wrong when several days before a weekend. I can't figure where I have gone wrong. If there is a simpler way or anyone can help would be greatly appreciated. thanks


See More: How to calculate minutes between two dates/times

Report •


#1
May 14, 2013 at 07:24:31
This link showed up in the related solutions below your post when I first viewed it. I think this may have your answers. Let me know if you have further questions regarding this issue.

http://www.computing.net/answers/of...

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#2
May 14, 2013 at 11:54:17
What you want to do can be done, but it is not a simple task,
the link that Newbie10 gave you will solve part of your problem,
but not all.

See here for a more complete solution:

http://www.cpearson.com/excel/DateT...

MIKE

http://www.skeptic.com/


Report •

#3
May 14, 2013 at 14:37:19
✔ Best Answer
In case looking at Mr. Pearson's page gave you a minor heart attack,
here is a trimmed down version that should do all you want.

First we need to Define Names for three cells:
Somewhere out of the way like cells AA AB & AC

In cell AA enter: The Time your work day Starts, IE 8:30 AM
In cell AB enter: The Time your work day Ends, IE: 5:00 PM
In Column AC enter: Any Holidays that need to be accounted for,
IE 01/01/2014, 12/25/2014, etc. etc.

Next we will give Names to each cell, that way if your start time changes,
or your closeing time changes, this stuff is not hard coded into the formulas.

How to Define Name in 2007:

On the Ribbon, Select Formulas
Select Define Name
In the Name Box enter: DayStart
In the Refers to Box (At the bottom) enter: =Sheet1!$AA$1
Click OK

On the Ribbon, Select Formulas
Select Define Name
In the Name Box enter: DayEnd
In the Refers to Box (At the bottom) enter: =Sheet1!$AB$1
Click OK

On the Ribbon, Select Formulas
Select Define Name
In the Name Box enter: HolidayList
In the Refers to Box (At the bottom) enter: =Sheet1!$AC$1:$AC$2
Click OK
Since I only have two holidays listed, I need only two cells, modify to suit your needs.
If you have no holidays ( I feel bad for you ) and you can just leave cell AC1 blank.

Now with your data looking like:

             A                    B             C                D
1) Wednesday, May 01, 2013      14:00   Monday, May 06, 2013    9:00

In cell E1 enter the formula:

=(INT(NETWORKDAYS(A1+1,C1-1,HolidayList)*8.5+((DayEnd-B1)+(D1-DayStart))*24))&" Hours "&TEXT(MOD(((DayEnd-B1)+(D1-DayStart))*24,1)/(24),"HH:MM")&" Minutes"

It's a bit long, so you might want to just copy/paste from here.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 22, 2013 at 23:41:05
thanks everyone sorry not to acknowledge your help sooner. Been away from computer a bit this last week due to health. Worked through all the suggestions. Cut and Paste worked well thanks Sceptic. Between that and the Pearson Page I think I have it working pretty good now. Again thanks all.

Report •

#5
May 22, 2013 at 23:43:26
Sorry that should read thanks Mike with referring to Pearson and Skeptic links. Which I used,

Report •


Ask Question