Time Calculation

[sunshine28637]

I need to find a formula that will calculate the amount of time worked in a day. For example... In cell A1 you would have 8:00 - 5:00, then I want cell B1 to show that is representative of 8 hours. What is the easiest and best way to do this? Thanks!

 

[Russ At Index]

Hi sunshine28637, Welcome To The Board

The link below i have found to be brilliant.

I hope you find it just as informative with regard
to time issues as i have !!!!

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Russ

 

[NBVC]

Try this in B1

=((TIMEVALUE(RIGHT(A1,FIND("- ",A1)-2))+0.5)-TIMEVALUE(LEFT(A1,FIND(" -",A1))))*24

EDIT

Upon further testing, I noted the formula I prescribed above didn't always work properly, e.g. 9:00 - 12:00 gave incorrect answer,

Here is an updated formula:

=IF(TIMEVALUE(RIGHT(A1,FIND("- ",A1)-1))>TIMEVALUE(LEFT(A1,FIND(" -",A1))),(TIMEVALUE(RIGHT(A1,FIND("- ",A1)-1))-TIMEVALUE(LEFT(A1,FIND(" -",A1))))*24,((TIMEVALUE(RIGHT(A1,FIND("- ",A1)-1))+0.5)-TIMEVALUE(LEFT(A1,FIND(" -",A1))))*24)

 

[sunshine28637]

Thanks so much for your help! It worked perfect.

 

[Greg Truby]

ADVERTISEMENT

As a general rule of thumb, combining two data points into one cell as text is an ill-advised structure. Much better to split the two data points (start time and end time in this case) into two columns. This makes any subsequent math functions much easier. Otherwise you end up jumping through a lot of hoops to do anything meaningful with the numbers, witness NBVC's formula for simply pulling the difference.

Furthermore times should be formatted to include am/pm. Would 8:00 - 10:00 be a 2 hour shift or 14? We can't tell. Format should be 8:00 am - 10:00 pm or 8:00 - 22:00 then we can tell.

 

[NBVC]

Yes Greg, that's right. I had fun coming up with that formula though.

I probably should have mentioned your suggestion, but I assumed that the OP had their own reasons for not splitting the cells up.

You know what assuming does...

 

[Greg Truby]

ADVERTISEMENT

Actually, in your formula I would not have multiplied the answer by 24. I would have left it as a true time value and formatted the cell as hh:mm that way any time differences that were less than whole hours would not show as decimals, i.e. 7:30 or 8:15 instead of 7.5 and 8.25 respectively.

And just a formula hint -- you could have simply used «conditionalstatement» / 2 in the formula instead of the more verbose IF() function.

 

[NBVC]

Thanks for the helpful tips, Greg.

This site has taught me a lot since I've been frequenting it. I thought I knew Excel well enough, but boy was I wrong. There is always somebody posting something that baffles my mind. But it's great!

Just didn't quite catch what you meant by «conditionalstatement» / 2. Can you show me an example perhaps?

 

[sunshine28637]

Thanks again for everyone's help! It works perfectly.

 

[sunshine28637]

Thanks again for everyone's help! It works perfectly.