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

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.

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

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 & ACIn 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 OKOn 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 OKOn 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:00In 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

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.

Sorry that should read thanks Mike with referring to Pearson and Skeptic links. Which I used,

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History