Hey numbers-pros!
I have created a shortcut which exports my appointments from the calendar app to calculate the sum of hours I spend for each of my clients.
Unfortunately, there are 2 different formats as you can see in the attached picture. I tried to change the cell format and also to convert it with the DUR2HOURS formular. None of it worked.
Anyone have any ideas?
iPad Air, iPadOS 14
The cells in column A next to the errors in column B have times of day in them, not durations. The others are durations only because there is no 30th hour in a day so Numbers interpreted those ones as durations instead. When you type something like 1:15 into a cell, Numbers first assumes it is a time of day. If it cannot be a time of day, it will assume it is a duration. If you pre-format the cells as a duration (before typing anything into them), Numbers will know what it is supposed to be and won't mess it up. However, it is recommended with durations that you enter them in the format 1m15s not 1:15 so there is no ambiguity in what you mean.
To fix the current problem, you cannot select these cells and change the format to duration. You will have to type a duration into each cell. Type them in like 1m15s. Or you can delete the content of those cells, format them as a duration, then type in a duration.
The cells in column A next to the errors in column B have times of day in them, not durations. The others are durations only because there is no 30th hour in a day so Numbers interpreted those ones as durations instead. When you type something like 1:15 into a cell, Numbers first assumes it is a time of day. If it cannot be a time of day, it will assume it is a duration. If you pre-format the cells as a duration (before typing anything into them), Numbers will know what it is supposed to be and won't mess it up. However, it is recommended with durations that you enter them in the format 1m15s not 1:15 so there is no ambiguity in what you mean.
To fix the current problem, you cannot select these cells and change the format to duration. You will have to type a duration into each cell. Type them in like 1m15s. Or you can delete the content of those cells, format them as a duration, then type in a duration.
Thank you, Badunit!
I realised it, after I wrote the post. I’ve converted the appointment date in the shortcut app, and now it all works just perfect!
Calculate time / convert duration
