You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers will not keep format, even when applied to cells with no data

I'm trying to get the cells to be formatted for Duration. No time. No date, just hours minutes. I apply that to empty cells, but then it keeps reverting to "Automatic." I have two columns, trying to format both for Duration so I can subtract one from the other. But when I try to subtract one from the other it says I can't subtract a number from a date format. Yet I did not format a date format. Numbers pretends one is a date format even though I have tried numerous times to make it Duration.


Why does this not work? In Excel it works fine.


This is a very basic function so I can't imagine any version etc applies. It should just work in the most basic fashion.

Posted on Jun 16, 2022 5:00 PM

Reply
Question marked as Top-ranking reply

Posted on Jun 17, 2022 10:08 AM

joerockbottom wrote:

I'm trying to get the cells to be formatted for Duration. No time. No date, just hours minutes. I apply that to empty cells, but then it keeps reverting to "Automatic." I have two columns, trying to format both for Duration so I can subtract one from the other. But when I try to subtract one from the other it says I can't subtract a number from a date format.


As you have probably discovered Numbers and Excel handle date-time differently.


Excel uses a serial number.


Numbers uses a date-time string. Also, Numbers has a Duration data type that Excel does not have.


To enter a Duration (a span of time) in Numbers you can't use special notation that doesn't seem to be very well documented.


To enter 10 hours 5 minutes you would type 10h5m. You can then format that as 10:05 if you want under the Cell pane Data Format pane. But use 10h5m to enter.


You can subtract a Number from a Date-Time value. That gives you a Duration. Here's a table showing that and subtraction of a Duration from a Duration giving a Duration, and a Date-Time from a Date-Time, also giving a Duration.




SG


Similar questions

2 replies
Question marked as Top-ranking reply

Jun 17, 2022 10:08 AM in response to joerockbottom

joerockbottom wrote:

I'm trying to get the cells to be formatted for Duration. No time. No date, just hours minutes. I apply that to empty cells, but then it keeps reverting to "Automatic." I have two columns, trying to format both for Duration so I can subtract one from the other. But when I try to subtract one from the other it says I can't subtract a number from a date format.


As you have probably discovered Numbers and Excel handle date-time differently.


Excel uses a serial number.


Numbers uses a date-time string. Also, Numbers has a Duration data type that Excel does not have.


To enter a Duration (a span of time) in Numbers you can't use special notation that doesn't seem to be very well documented.


To enter 10 hours 5 minutes you would type 10h5m. You can then format that as 10:05 if you want under the Cell pane Data Format pane. But use 10h5m to enter.


You can subtract a Number from a Date-Time value. That gives you a Duration. Here's a table showing that and subtraction of a Duration from a Duration giving a Duration, and a Date-Time from a Date-Time, also giving a Duration.




SG


Jun 16, 2022 5:35 PM in response to joerockbottom

If you format a cell as duration but type something into the cell that is not a duration, the cell will revert to "automatic". Is that what is going on? Can you give an example of what you are typing into these cells?


What is it you are trying to do? Subtract two times of day to get a duration or subtract two durations to get their difference?

Numbers will not keep format, even when applied to cells with no data

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.