#1
|
|||
|
|||
Excel convert format [h]:mm:ss to decimal
Hi expert,
I have a cell e.g I14 in format [h]:mm:ss and it is showing 178:50:34. I want to convert it so that the number of hour (178) multiply by 10. Which should be equal to 1780. So, i type a formula in another cell which is : =HOUR(I14)*10 However, it show 2400:00:00 and the format of this cell is [h]:mm:ss which is not my expected. Please advice? Regards Grace |
#2
|
||||
|
||||
Hi Grace,
The problem occurs as a combination of the format and the way the function works. The Excel HOUR function will read/convert your time entry (178:50:34) as (1900-01-07 10:50:34) and return a result of 10 hours because that's how many hours are in the current day for that time entry. Then, Excel will assume you want to continue using your format of "[h]:mm:ss", and convert the 10 to 240 because that's how many hours are in 10 days. The solution will depend on what you intend to do with your result (1780 hours). Do you want Excel to treat this as a number or as a length of time? If you just want the number, then change the formula to this: Code:
=DAY(I14)*24+HOUR(I14) Cheers, |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help in format or link in excel | handsome1968 | Excel | 1 | 05-01-2010 09:20 AM |
Format Excel to look like Word | Kilconey | Excel | 1 | 04-30-2010 09:42 AM |
Convert hours in decimal | ghostones | Excel | 1 | 12-29-2009 09:17 PM |
Merge, Too many Numbers right of the Decimal Point | ohdearme | Mail Merge | 0 | 10-25-2009 05:10 PM |
Convert a file from HTML to WORD format weblayout view | gtselvam | Word | 0 | 12-02-2008 03:53 AM |