PDA

View Full Version : Excel for Susan

Shroeder
29-11-2002, 03:47 PM
Hey Susan, how did your issue with hh:mm:ss in excel go?

If you got it sorted, any idea on how to adjust that format to a percentage of hours

eg. so 02:45:00 comes out as 2.75

Ta

wuppo
29-11-2002, 04:22 PM
Time values in Excel are stored as a decimal of 24 hours. i.e 0:00:00 hours = 0.0000 and 23:59:59 = 0.99999999

So if you reference a time value and multiply by 24, and format the value to general with 2 dec places, you will get the desired.

Susan B
29-11-2002, 04:25 PM
ummm I haven't finished sorting that problem out yet. :8} Excel is on the other PC but I'll have a look later.

I have got part of the IF formula working by inserting another column that displays the time format in a number format. What I have to do now is complete the rest of the IF formula.

Will get back to you on this later on. :-)

Susan B
29-11-2002, 04:27 PM
Oh, you got there before me Wuppo.... :-)

Yes, that's exactly what I've done - multiply by 24.

Graham L
29-11-2002, 04:28 PM
It would be so much easier if the French Revolution improved decimal time had caught on. :D Some people wouldn't like the 10 day weeks.

Big John
30-11-2002, 12:31 AM
Another way is to use the built in excel function TIMEVALUE()

IE IF(\$A\$3<TIMEVALUE("10:40:00"),"Less the 10:40","Greater or Equal to 10:40")

if the Cell A3 contains a time of 06:24:00 then it will return the true value
if it contained 10:43:00 then it would return the false value