Something is not right in Excel

[philiphales]

I have for instance 2.92 as a number of hours. I can take the 0.92 and multiply by 60 giving me 55.2 minutes then take the 0.2 and multiply by 60 giving me 12. So we all agree that 2.92 hours is 2hrs 55mins 12 seconds.
However, if I use =INT((A1)) & " Hours and " & MINUTE(A1-INT(A1)) & " Minutes and " & SECOND(A1) & " Seconds" [assuming of course that the 2.92 is in cell A1] I get 2hrs 4mins 48secs!!!

If I curtail the formula to just =INT((A1)) & " Hours and " & MINUTE(A1) & " Minutes" still gives me 2hrs 4mins

Can someone elaborate on what I may be doing wrong, please.

 

[NARAYANK991]

Hi ,

For the HOUR , MINUTE and SECOND functions to return the correct results , the values passed to them have to be one of the following :

1. Time string enclosed in quotation marks , as in "02:55:12"

2. Time values derived using the TIMEVALUE function , as in =TIMEVALUE("02:55:12")

3. Decimal time values , where 0 represents midnight , and 1 represents the next midnight.

2.92 is none of the above.

You need to use 2.92/24 to get at the real time value of 2 hours , 55 minutes and 12 seconds.

Narayan

 

[NARAYANK991]

Hi ,

Thus the formula :

=HOUR(A1) & " Hours and " & MINUTE(A1) & " Minutes and " & SECOND(A1) & " seconds"

will return the correct result , if A1 contains the value =2.92/24

Narayan

 

[philiphales]

Thanks for that, however I have a situation where I now have 24.93hours.

If I / by 24 I get 1.04 which gives me 0 hours and 56 minutes 0 seconds, how do I recover the lost 24 hours [24.93 being 24hrs 55mins 48secs]?

This time to decimal in Excel is doing my head in!!

I have a corresponding column adjacent to this total in time and the total is 24:56:00 which using =INT(A1) &" Days " & INT(MOD(A1,INT(A1))*24) & " Hours and " & MINUTE(A1) & " Minutes" does give me 1 day 0 hrs 56 mins however, my reason for converting to decimal is that I apply cost per hour, but I wanted to add the decimaled time and convert to hr, mm, ss so that I can write that a delay of 24hrs 55mins 48secs has cost £X.

 

[vletm]

cell A2 is Your value 24,93
cell B2 formula is = A2/24 with custom format [hh]:mm:ss (B3)
cell C2 formula is = A2/24 (including more decimals)

ideas? questions?

 

[NARAYANK991]

Thanks for that, however I have a situation where I now have 24.93hours.

If I / by 24 I get 1.04 which gives me 0 hours and 56 minutes 0 seconds, how do I recover the lost 24 hours [24.93 being 24hrs 55mins 48secs]?

Hi ,

If you have a value such as 24.93 , you can always subtract 24 from it to be left with 0.93 , which when divided by 24 gives 0.03875

Using the same formula as before on this value :

=HOUR(A1) & " Hours and " & MINUTE(A1) & " Minutes and " & SECOND(A1) & " seconds"

where A1 contains =0.93/24 , will give :

0 Hours & 55 Minutes & 48 seconds

You can always use the formula =INT(value / 24) * 24 to get the number of hours which are a multiple of 24 hours.

Narayan