# Time Calculation

#### sunshine28637

##### New Member
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!

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### NBVC

##### Well-known Member
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

##### New Member
Thanks so much for your help! It worked perfect.

#### Greg Truby

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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

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

#### sunshine28637

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

Replies
32
Views
437
Replies
22
Views
254
Replies
1
Views
155
Replies
10
Views
467
Replies
1
Views
244

1,195,588
Messages
6,010,606
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back