User profile for user: klavezzo
klavezzo Author
User level: Level 1
10 points

Date-Time Arithmetic

I'm a long time Excel user. I want to import a few of my Excel spreadsheets into Numbers so I can then take advantage of iCloud Drive to add data to these particular Numbers spreadsheet from my iPad Air 2 (latest version of IOS 8). Any modifications to the Numbers spreadsheet will be done on my 2 year old 27" iMac running the latest version of Yosemite and Numbers.


All my Excel spreadsheets have imported to Numbers without a hitch except the one that uses Date-Time arithmetic. Excel allows you to put a date in one cell and time in another and to add the two to create a number based on January 1, 1904 as the reference. This makes it easy to do Date-Time Arithmetic. As best I can tell you can only do this in Numbers if you enter a date and a time in each cell, which is both cumbersome and time consuming. I can't seem to find any way around this. Is there a way to add a date cell to a time cell in Numbers similar to Excel that I haven't found? Thanks.

iMac (27-inch, Late 2012), OS X Yosemite (10.10.1)

Posted on Jan 22, 2015 7:16 PM

Reply
8 replies
User profile for user: Jerrold Green1
Jerrold Green1
User level: Level 7
30,274 points

Jan 22, 2015 9:20 PM in response to klavezzo

Klavezzo,


Date-Time values are where Numbers diverges most from Excel in the things that I use frequently. I no longer use Excel, so the differences don't affect me the way they might affect you or others. In Numbers, every Date has a Time associated with it, and every Time has a Date associated with it. They always exist together.


There are functions to strip out individual elements of the Date-Time value, if you need to: YEAR, MONTH, DAY, HOUR, MINUTE and TIMEVALUE. The first 5 are self explanatory, TIMEVALUE returns the decimal fraction of a day corresponding to the Time portion of the Date-Time value.


A Duration value can be useful if you prefer not to work in decimal fractions of a day, and it is especially important for Date-Time difference calculations.


The key things to remember about Date-Time calculations are 1. You can't add two Date-Time values, and 2. The result of a Date-Time subtraction is a Duration, and 3. To add to a Date-Time value, you can either add a Duration or you can add a numeric value of days, and decimal fractions of a day, for the time portion.


As with most values in Numbers, you can change how a value is displayed with formatting, but you can't change the data type with formatting. The only exception that I can think of is that when a value is formatted as Text. Then it no longer can be manipulated as a value, so it truly is altered internally.


If you give us some examples of your calculations, we can suggest ways to deal with them in Numbers.


Jerry

Reply
User profile for user: Jerrold Green1
Jerrold Green1
User level: Level 7
30,274 points

Jan 23, 2015 2:57 PM in response to klavezzo

Klave,


Feel free to give us some examples of date-time arithmetic you are dealing with and we can work through the expressions with you.


One thing I probably don't need to mention, but for clarity I will: Subtracting from a date-time value is the same as adding to one, except for the design of the value being added. And, there are many functions for manipulating dates and times, like EOMONTH which accounts for the different number of days depending on the month(s) involved.


J.

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Date-Time Arithmetic

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