# Calculating time spent between two cells

#### jackleg

##### New Member
Ok I'm a little rusty here. Anyone know how to make this formula work correctly?

I am making a timesheet spreadsheet that lets you enter times in cells and then, and here's the prob, calculate the hours.

It works fine except that I want the result cell to be normal numbers (2.5 hours, not 2:30 hours if it were 7:00 in G8 and 9:30 in H8) I can't figure out how to format the cell to give a number like that. If I format as number the number comes out as some weird stuff derived from the time.

Here's where I'm at:
=IF(H8>G8,IF(G8>0,H8-G8, ""),IF(G8>0,(H8+24)-G8,""))

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

##### MrExcel MVP
Hi - welcome to the board!

multiply it all by 24...

#### jackleg

##### New Member
Thanks for your reply. That helped me in one sense but brought on another problem. So let me rephrase the question and ask how you all would do this.

As I said I'm making a time tracking sheet. Times will be entered in (for example)A1 (Start) and A2 (Finish)in 24 hour time format.

A3 needs to be a total in number format (1.25, 1.5, etc).

So I need 8:00 in A1 and 9:00 in A2 to equal 1.0 in A3. This works ok until I get to times like 18:00 in A1 and 1:00 in A2. This calculates out to 23.29 in my formula which is clearly not right.

What am I missing here??? Thanks in advance

##### MrExcel MVP
times are held as decimal fractions of days - 0.5 is midday etc. times entered without a date are assumed to have a date value of zero (check this by formatting one of your date cewlls with a time). for time calculations where you're subtracting one time from another,(entered without a date), & the next time is after midnight (i.e. on the next day), you need to add 1 to the earlier time...

http://www.ozgrid.com/Excel
& scroll down the introduction page to the 'Date & times' page

http://www.mvps.org/dmcritchie/excel/datetime.htm
for all sorts of stuff on date / time calculations

#### jackleg

##### New Member
Thanks for your help. It's working now. That one always puzzled me. One more wrinkle in the brain

#### 60secondsguitar

##### New Member
Hi, I am trying to do a formula like the gentleman before me but mine is different and I can't make it work. Here is what I am doing.

A1 is time in. A2 is time out. A3 is total minutes. I need the total minutes to be in a number so I can multiply it by .25 and get a dollar amount in A4. The .25 is the amount the person gets per minute for working. she gets \$15.00 per hour thus .25 per minute.

##### MrExcel MVP
Hi.

1) You should start a new thread for a new issue, not hijack an onld one (if it's relevant, just include a link in your new thread)
2) with, e.g. 09:30:00 in a1, 17:45:00 in a2, then:

=(a2-a1)*(24*60)

...in a3 returns 495, being the number of minutes elapsed as requested.

#### 60secondsguitar

##### New Member
thanks for the help. I really appreciate it.

Replies
25
Views
563
Replies
2
Views
221
Replies
3
Views
232
Replies
5
Views
405
Replies
4
Views
117

1,181,105
Messages
5,928,098
Members
436,588
Latest member
mummabare

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