Log In
Register
Home
December 12, 2016
Offline
In addition to hh:mm:ss I tried to write a set of shorter format mm:ss and ss to cope with the case when a time duration(difference) is less than one hour or even one minute. It's simple by using TEXT formula in EXCEL but failed with FOMAT formula in VBA. I've made a comparison in worksheet "countdown" cell C1 and C2. To tackle this issue I converted the time duration(difference) in general number format back to the number of min and sec each by dividing 1/24/60 and 1/24/60/60 using ROUND, MOD, and FORMAT together. The problem was that MOD in VBA just returns integer.
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
Hi Julian,
In excel, 1 day represents 1 unit. A fragment of a day will obviously be a fragment of 1 unit. Therefore, if 1 day=1, half day will be =0.5 (or =1/2).
One hour will be =1/24, because 1 day has obviously 24 hours.
in ell A1, you have a value of 6.7xxxx, which is over 6 days. I you want to format the value to be displayed in minutes, hours, seconds, you have to use the square brackets to display times exceeding 24 hours / 60 minutes / 60 seconds, otherwise it will show only the portion exceeding 60 minutes for example.
0.049567, if you use =TEXT(A1,"mm:ss"), will display 11:23, but if you use =TEXT(A1,"[mm]:ss"), will display 71:23, and this is the correct result that will not ignore the time exceeding 60 minutes.
Another example:
0.0007567 represents 65 seconds, but if you use =TEXT(A1,"ss"), it will show you just 5 seconds. Use =TEXT(A1,"[ss]") to display the entire value.
Answers Post
1 Guest(s)
