Calculating time spent between two cells

jackleg

New Member
Joined
Oct 13, 2002
Messages
6
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jackleg

New Member
Joined
Oct 13, 2002
Messages
6
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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...

see also

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

paddy
 

jackleg

New Member
Joined
Oct 13, 2002
Messages
6

ADVERTISEMENT

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

60secondsguitar

New Member
Joined
Jul 13, 2010
Messages
4
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.

Thanks for your help.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

Forum statistics

Threads
1,144,119
Messages
5,722,592
Members
422,447
Latest member
knopp

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top