Solved Business day+ hours calculation

December 24, 2019 at 00:41:25
Specs: Windows 7
Please DerbyDad.. I want to calculate a transactions aging between CELL A TIME RECIEVED (MM/DD/YYYY HH:MM) to CELL B TIME FINISHED (MM/DD/YYY HH:MM) excluding friday and saturday ( business week sunday to thursday) and only including business hours 07:30AM to 03:00PM

See More: Business day+ hours calculation

Report •

#1
December 24, 2019 at 09:25:23
✔ Best Answer
I'm not DerbyDad, but try here:

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

Going away for the holiday so won't be around to help till the weekend.

MIKE

http://www.skeptic.com/


Report •

#2
December 24, 2019 at 20:27:23
Thank you. I said Derby because he was one of the best ive seen on this forum. Happy Holidays/Merry Christmas. I tried your formula it really does work. But I cant go further The issue I have with your formula is that it returns a string. And in a string I can't calculate averages or total hours etc.. or plug in the times in a Pivot to show for example the average time an employee created 10 transactions etc...

Report •

#3
December 25, 2019 at 06:10:17
Actually, Mike is one of the best at time and date formulas. I usually leave those types of questions to him.

When posting in a help forum such as this, it would probably be best if you just posted your question to the forum in general without directing it to any individual. That way anyone can pick it up run with it.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
December 25, 2019 at 08:08:43
re: "And in a string I can't calculate averages or total hours etc"

Your next step would be to extract the numerical values from the string so you can do your calculations.

I don't have your data, so I'll use the string from the site that Mike suggested and break it down into sections, days then hours:

In A1 I entered the string 3 days 2 hours

Extracting Days, Converting to Hours:

To extract the number of days, use the LEFT function to extract all characters up to, but not including the first space:

=LEFT(A1,FIND(" ",A1)-1)

While this will still return a text string, Excel loves working with numbers, so it will allow you to use a "text number" in a calculation:

=LEFT(A1,FIND(" ",A1)-1)*24

That will return 72 since 3*24 = 72

Extracting Hours

There are various ways to extract the hours from that string, but I'm going to suggest a method that is more versatile than most and can be used in many other instances. This generic formula will extract the string of characters between any 2 delimiters, with the delimiter and the field to be extracted specified by the user. In the example we are using, the delimiter will be a space " " and we want the 3rd field, i.e. the string between the 2nd and 3rd delimiter.


Field Number -->   1   2    3   4 
                   2  days  3  hours

The generic formula is explained here:

http://www.excelfox.com/forum/showt...

=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",99)),fieldnumber*99-98,99))

Our formula, after replacing the "delimiter" and "fieldnumber" with the proper values:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),3*99-98,99))

From that you will get the the text string "2", which can then be multiplied by 1 to get the numerical value of 2.

Extracting Total Hours

Combining everything into 1 formula, we get 74, which is the number of hours in 3 days 2 hours:

=LEFT(A1,FIND(" ",A1))*24+TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),3*99-98,99))*1

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
December 25, 2019 at 10:53:28
These two chaps are two of the best around; ably supported at times by several other here.

I always track their input as frequently - no"always" it's learning curve of value.

How they find the time between hot springs and vino by one, and go-cart racing by the other is...?


Report •

#6
December 25, 2019 at 16:49:28
Thanks trvlr! Happy Holidays.

Just for accuracy, it's not go-cart racing, it's Soap Box Derby racing. Now that the kids are "all growed up" it takes up a lot less of my time. Just a few volunteer hours every now and then.

The white car, #35, is my son's World Championship car, hanging in the Soap Box Derby Hall Of Fame in Akron, OH.

https://ccnwordpress.blob.core.wind...

message edited by DerbyDad03


Report •

#7
December 25, 2019 at 23:20:47
Thank you. I have read so so many of mmcconaghy and DerbyDad03 excel answers on this forum. You guys are on another level. Thank you guys for helping all the people on this forum!

Report •

Ask Question