Hi All,
I'm using Numbers version 11.1 (7031.0.102)
My spreadsheet tracks time duration in minutes:seconds (00:00).
If I add a row or cell - forget it - i simply cannot set the cell to this format. It reverts to a date format, which i have no idea why.
is this a bug or am i doing something wrong?
Thanks!
-Gary.
MacBook Pro 16″, macOS 11.5
imgaryshap wrote:
The entire cell format changes to "Automatic". so i type "00:10" for min: sec,
But that's my point!
Don't type "00:10".
Try typing "0m 10s".
(without the quotes of course).
Once you've entered it thus using duration input notation, you can then use the inspector panel to change the display format to 00:10 if you like, though I tend to leave it as unambiguous duration format.
SG
imgaryshap wrote:
The entire cell format changes to "Automatic". so i type "00:10" for min: sec,
But that's my point!
Don't type "00:10".
Try typing "0m 10s".
(without the quotes of course).
Once you've entered it thus using duration input notation, you can then use the inspector panel to change the display format to 00:10 if you like, though I tend to leave it as unambiguous duration format.
SG
imgaryshap wrote:
Hi All,
My spreadsheet tracks time duration in minutes:seconds (00:00).
If I add a row or cell - forget it - i simply cannot set the cell to this format. It reverts to a date format,
-Gary.
Are you entering duration in the added cell?
If so type something like 11m 5s to enter the value.
You can set that to display as 11:05 if you want (or use other native duration formats)
But entering 11:05 is ambiguous and make Numbers think you mean a time.
SG
I was typing a reply as you were posting.
If you try to change the format of a cell to something it is not, Numbers will reject it. If you change it, click on another cell, then reselect the cell and the format did not change, that may be the reason (barring some bug or other problem). As an example, I cannot format a cell as duration if it has a date&time value in it. Could this be the problem?
If the cell format is "automatic" and you type 12:34 or some other value that could be a time of day, it is going to be interpreted as a time of day, not a duration. Once it has been interpreted as a time of day, you cannot reformat that 12:34 into a duration. You have to delete it, format the cell as a duration (assuming you want the 00:00 format), then enter it again. If you are okay with 12m34s format, you can simply type 12m34s into the cell and it will see that as a duration and format the cell as a duration (but in 12m34s format, not as 12:34).
What you are describing is not usual for a cell that should be pre-formatted as duration. So either the new row is not getting the cell format or the data you are typing into the cell is being rejected as a duration (but is acceptable as a time of day).
What method are you using to insert a new row? And what do you mean by "add a cell"? You cannot insert individual cells in Numbers, only entire rows or columns so "add a cell" doesn't make sense. If you have a row or cell selected and then do "add row below", the new row is supposed to get the same cell formatting. Same with if you add a row above. Same idea if you add a column.
After adding a new row, look at the cell format for that duration cell and see if it is set to duration, not automatic or date&time. Check it before you type anything into the cell.
Assuming the above is working correctly and the cell starts out formatted as duration, what are you typing into the cell that is turning it into a date&time? I have a cell formatted as duration with units of min:sec displayed as 00:00. When I type 12:34 it is taken as the duration 12 minutes and 34 seconds and shows it in the cell as 12:34. Same when I type 12m34s.
I'm not frustrated. I am confused what the problem is, though. If it is turning the number 10 into a time, not using it as the number 10, the cell is absolutely not acting like it is set to automatic. I know the questions below sound like what you already answered but it might help to post an image or two.
One thing you might try in the mean time is to add a new row somewhere in the middle of your other data and see if it has the same problem as adding a row to the bottom. If there is some problem in the file, it might be possible to copy/paste a good row from above to fix the problem below. Just a thought.
yeah. of course. I'm setting the cell parameter at "Duration" and how I want it to be formatted. The entire cell format changes to "Automatic". so i type "00:10" for min: sec, the moment I leave the cell it changes to something else.
One work around I have discovered is that i have to go into the cell > "Clear All" > then reformat it. Every time. It's a real pain...
You started your description of the problem with "if I add a row..." so that was where I started.
What you are describing is not normal behavior. So far it is not clear why it is happening. SGIII is telling you a way to get around it. I'm trying to figure out what is going on. I have the same version of Numbers but am so far unable to replicate the problem.
If you set the format to duration then click on another cell then reselect the cell you just formatted, did the format stick? Is it still duration with the display format you chose?
What about all the other duration cells in your spreadsheet? Are they all working fine or all they all doing it?
You mentioned entering the number 10 (by itself, not as 10:00) and it being turned into a time of day. Does this happen if the cell is set to automatic or only when it is set to duration? I find that to be really strange.
What is your language and region? Not that it should matter but who knows.
:30 is not a duration, it is text.
0:30 is 30 seconds
512 is 512 minutes
.5 is 30 seconds (1/2 minute)
1.5 is 1 minute 30 seconds (1 1/2 minute)
5:12 is 5 minutes 12 seconds
0:512 is 8 minutes 32 seconds
and lastly- if i delete the information > change parameter to Duration > and then retype data, same problem. Delete isnt enough. I have to “Clear All.”
I’ll give it shot. with that said, it should not happen. if i set a cell’s formatting as Duration, it should stay as I set it and not assume I’m typing in a time. If it’s assuming I’m writing something else after i tell it what the information I’m typing should be, then it’s a software bug.
sorry for the confusion. add a row, not cell. but you are getting too hung up on that.
here’s the deal - im setting the format of the cell to Duration and the display to 00:00 for (in my needs) min:sec.
for the duration setting, it does not matter. Numbers ignores it.
for example - this problem does not happen when I set the parameter to currency. If i set the parameters of the cell to US dollars with 2 decimal points and type in “100”, the cell displays “$100.00”. if i type “12.3”, it displays “$12.30”. Numbers does not switch it to a date or something I did NOT set.
so when i set the parameters as Duration and set my display as 00:00, i want the same response. if I type in “10” or “00:10” it should display “00:10” (even “10:00” would be acceptable), NOT change the whole thing to date and time or whatever it may think it is. I’m TELLING it what it is, and it should leave the parameter set accordingly.
Duration is buggy.
US. English.
we’re going in circles.
yes, i add a row to a spreadsheet that is calculating in duration.
i set the new cells to Duration (min: sec) because adding a row does not automatically maintain other cell parameters.
it does not work.
the cells behave as if in Automatic. setting Duration as a parameter down to the min:sec does not work. i believe setting a parameter should make my work flow faster. i should not have to type “5m 12s”. i should be able to type “312” and the calculation should happen and the display i set should read 05:12. or if i type 10, 00:10.
that’s the point of parameters to me.
instead, the cells are behaving as if in automatic mode and not retaining the parameter i set regardless of how i type the number.
thanks again.
I don't want to waste your time or mine. The app works just as you want it to and all I was trying to do was figure out why it is not working for you. We were not going in circles. You may think I asked questions you already answered but your statements and answers have been ambiguous and in some cases contradictory. Example: "If I add a row or cell - forget it - ... it reverts to a date format." versus "I'm setting the cell parameter at Duration ... the entire cell format changes to Automatic". Date format or automatic? I don't know. Could be important. And you can have a format of "automatic" that also has the date and time settings, which makes the cell date & time. Regardless, there may be something screwed up in this particular document of yours that no questions and answers are going to fix.
I'm sorry if i'm not articulating myself clearly enough. I tend to use "forget it" conversationally not literally. As in "F'get about it, it doesn't work." That's on me. The cell in my document behaves as if set to Automatic, and it does so only when I set the parameter to Duration. If I set the parameter to anythings else - Text, Date, Number, Currency, etc - the parameter stays locked. When I set the parameter to Duration, it behaves as if in Automatic. I don't know how else to make that any more clear. I did thank you for your time, and do thank you for your time. If I'm still frustrating you, please feel free to leave the thread.
Thanks. I understand all this.
the bottom line for me - and what I've come to discover - is that the DURATION parameter is not as intuitive as the others.
I'm of the belief that it should function better. For me, if the set parameter of a cell is Duration and one types ":30", the program should consider that empty space before the ":" as zero and function accordingly. The program should NOT take it upon itself to reset the parameter to Text or anything outside of what the user designated the cell to be. If the parameter is set to Automatic, then yes, the user is responsible to be more specific.
Again - SGill above's response is more accurate to the problem I'm having. As it turns out, Duration in Numbers is very particular, and not as fluid as many of the other parameters.
Thanks again.
using duration in numbers
