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.

Cell formatting in Numbers

I am trying to multiply two cells to get a product.


The first cell is formatted as hours:mins - a period of time


The second cells is formatted as a number - the number of times something occurs per hour


The product is the number of occurrences in the period of time.


When I multiply them the product is shown as hours:mins


The Data format for the product cell is Automatic. When I change the Data Format to Number the cell doesn't change and if I click away to another cell the product cell Data Format reverts to Automatic.


As a test I opened a new numbers sheet and tried to calculate the difference between two dates and it returned a result in days that I was unable to format as a number.


So there appears to be some general control that is overriding what I am asking it to do in Data Format.


Can anyone help please?


Thank you


David

iMac 27″, macOS 13.3

Posted on May 21, 2023 11:52 AM

Reply
Question marked as Top-ranking reply

Posted on May 22, 2023 12:52 AM

DavidO198 wrote:

As a test I opened a new numbers sheet and tried to calculate the difference between two dates and it returned a result in days that I was unable to format as a number.


Say, Date & Time 1 is in B2, and Date & Time 2 is in C2.


To get the number of days difference as a decimal number rather than a Duration you can simply use the close cousin of the DUR2HOURS function that Ian suggests for the other part of your question, something like this:


=DUR2DAYS(C2-B2).


In Numbers it can be easier to use the family of DUR2... functions than the DATEDIF function.


However if you will need to exchange documents with Excel users you may want to use DATEDIF, something like this:


=DATEDIF(B3,C3,"D")


Note that a "date" entered into Numbers always includes a time component too. If you don't supply a time then Numbers assumes the instant after midnight. Whether the time component is displayed or not depends on what you do in the Data Format dropdown in the Cell tab.


To learn more about the DUR2... functions, with lots of examples, you can go to Help > Formulas and Functions Help in your menu, or follow this link:


Formulas and Functions Help - Apple Support


SG






Similar questions

5 replies
Question marked as Top-ranking reply

May 22, 2023 12:52 AM in response to DavidO198

DavidO198 wrote:

As a test I opened a new numbers sheet and tried to calculate the difference between two dates and it returned a result in days that I was unable to format as a number.


Say, Date & Time 1 is in B2, and Date & Time 2 is in C2.


To get the number of days difference as a decimal number rather than a Duration you can simply use the close cousin of the DUR2HOURS function that Ian suggests for the other part of your question, something like this:


=DUR2DAYS(C2-B2).


In Numbers it can be easier to use the family of DUR2... functions than the DATEDIF function.


However if you will need to exchange documents with Excel users you may want to use DATEDIF, something like this:


=DATEDIF(B3,C3,"D")


Note that a "date" entered into Numbers always includes a time component too. If you don't supply a time then Numbers assumes the instant after midnight. Whether the time component is displayed or not depends on what you do in the Data Format dropdown in the Cell tab.


To learn more about the DUR2... functions, with lots of examples, you can go to Help > Formulas and Functions Help in your menu, or follow this link:


Formulas and Functions Help - Apple Support


SG






May 22, 2023 3:30 AM in response to DavidO198

Hi David,


Thanks for your feedback and the green tick!

One thing to watch out for if you are using 2:00 as a duration. It could be confused as Date & Time (time of day).


2:00 am under the 24 hour clock shows 2:00 in a cell.


A duration of 2 hours can also display 2:00 with the style set to 0:00


I prefer this style to avoid ambiguity:


Regards,

Ian.



May 21, 2023 5:47 PM in response to DavidO198

DavidO198 wrote:

I am trying to multiply two cells to get a product.

The first cell is formatted as hours:mins - a period of time

The second cells is formatted as a number - the number of times something occurs per hour

The product is the number of occurrences in the period of time.

Nope.


That’s what you would like the product to be. But I multiply a period of time (say 2 hours 30 minutes) by a number (say 3), the product is that duration times that number (7 hours 30 minutes).


As a test I opened a new numbers sheet and tried to calculate the difference between two dates

That is exactly what Numbers’ DATEDIF function does.

Cell formatting in Numbers

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