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.
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.
