Hello,
Cannot find the answer to this anywhere else, so sorry if it does appear somewhere on this forum.
I want to convert the following:
Cell A1 contains time format of hours and minutes. I want to place the decimalised equivalent of that time referenced in A1 in Cell A2
Example
Time HH/MM. 1h36m
Time Decimal. 1.6Hours
How do I do it?
Much appreciate your help
Jon
Here's one way, I'm sure there are others:
The durations are in column B. The formula in C2 is
ROUND(DUR2MINUTES(B2)÷60,2)&" Hours"
This is then filled down by dragging the yellow handle. (Other rounding methods are available.)
Here's one way, I'm sure there are others:
The durations are in column B. The formula in C2 is
ROUND(DUR2MINUTES(B2)÷60,2)&" Hours"
This is then filled down by dragging the yellow handle. (Other rounding methods are available.)
If you click on the red triangle it will give a brief description of the error:
If you cell C1 is empty, the calculation can't be performed. Two possible ways round this:
1) Click cell C2 and press delete. This will remove the formula and the error triangle - you can always fill down from B2 again if you later add data to C1.
2) Handle the error by wrapping the original formula (or rather SGill's much simpler DUR2HOURS() function) in an IFERROR() function:
IFERROR(ROUND(DUR2HOURS(B3),2)&" Hours","")
This will make the calculation if there is no error, but leave your C2 empty - the last "" - if there is nothing in C1 on which to base the calculation.
Here's the updated formula in my row 5:
Perfect, that worked thank you.
Maybe you could help with the next problem, this follows on from the previous.
A1 shows time in h:mm B1 shows time in h:mm C1 shows time in h:mm
A2 shows time in decimal B2, C2 etc etc
I may not need to use C1 and C2 or D1, D2 etc...So I delete the h:mm from C1 and C2 shows a red warning triangle, how do I show just a blank cell?
Many thanks again for all your help
Jon
😳
Time format to decimal time in Numbers for MAC