Calculating time spent between two cells

[jackleg]

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,""))

 

[PaddyD]

Hi - welcome to the board!

multiply it all by 24...

Paddy

 

[jackleg]

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]

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]

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

 

[60secondsguitar]

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]

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]

thanks for the help. I really appreciate it.