Excel Formula Question

joroma · 28 Jun 2006 at 08:48

One for the bright boys out there.

I've got a timesheet programme in EXCEL that adds up all my hours worked in hours and minutes giving me a total for the week of say 39hrs 42mins.

I want to display that total in another cell as a decimal ie 39.7hrs. Is there an easy way of doing that???

Hope this questions ok in this forum. If not mods are welcome to move/delete.

WillyNelson · 28 Jun 2006 at 11:06

What format is the original total in? ie xxHrs xxMin or xx:xx? Are the hours & minutes numbers in the same cell or different cells?

joroma · 28 Jun 2006 at 13:18

They are in the format xx:yy ie the cell is formatted to display hh:mm.

I want to display the time period as a decimal in a seperate cell.

WillyNelson · 28 Jun 2006 at 13:34

To convert a time less than 24hrs to decimal:
=(??-INT(??))*24
where ?? is the cell containing the original total

To sum a range of times to a decimal:
=SUM(range)*24 format as 'general'

joroma · 28 Jun 2006 at 13:41

Thanks Nelson that works a treat. I just need to try and understand how now................................

WillyNelson · 28 Jun 2006 at 13:46

Not quite sure myself...

joroma · 28 Jun 2006 at 15:09

My initial elation was premature. This only works if the total hours and minutes is less than 24!

I need to be able to convert 225hrs 45mins (displayed as 225:45) into 225.75hrs (displayed as 225.75). I've tried googling but can't find anything I can make work.

div0 · 28 Jun 2006 at 15:50

I think it all depends on what FORMAT each cell is?!?!

For example if I make A1 (Format - Cells - Custom - [h]:mm:ss) then I can enter a "time" greater than 24hrs. This "time" is ACTUALLY STORED as a DATE but displayed as a TIME.

If I make B1 (Format - Cells - General), then I can represent that DATE/TIME as a decimal number, simply by multiplying by 60.

eg:

A1 - type in 225:45 (gets displayed as 225:45:00, but is actually stored as 09/01/1900 09:45:00)

B1 - type in =A1*60 (gets displayed as 225.75)

Which seems to do what you want?!?!

But it really depends on what FORMAT your "numbers/time/dates" are actually using.

|Ric| · 28 Jun 2006 at 16:00

=MID(TEXT(A1, "[h]""hrs"" mm""mins"""), 1, FIND("hrs", TEXT(A1, "[h]""hrs"" mm""mins"""))-1) + MINUTE(A1)/60

With the time value to be formatted in A1

|Ric| · 28 Jun 2006 at 16:03

The problem is that Excel is storing the time differently to how you have chosen to display it. It always seems to treat it as a time on the 24 hour clock even if you use [h] to force it to display a number of hours > 23.
Whilst it is displayed this way, you can't use the data in this way. The formula above basically returns the formatted value as a text string that can then be manipulated. Its not particularly pretty but I think it is the only way to do it.

Ric.

|Ric| · 28 Jun 2006 at 16:05

lol that is completely unnessecary what I just did. Aslong as you store it as a time then:
=INT(TEXT(A1, "[h]"))+MINUTE(A1)/60
will achieve what you want

joroma · 28 Jun 2006 at 19:13

|Ric| said:
lol that is completely unnessecary what I just did. Aslong as you store it as a time then:
=INT(TEXT(A1, "[h]"))+MINUTE(A1)/60
will achieve what you want

Thanks Ric this appears to work fine. I'll do some more testing when I get back to the office tomorrow!