Table of Contents
Storage of Time/Date Values
...
Date | Unix Time Conversion |
---|---|
Jan 1,1970 00:00:01 | 1 |
Jan 1,2016 00:00:01 | 1451606401 |
Constants
A constant is an identifier whose associated value cannot typically be altered by the program during its execution.
Constant | Component |
---|---|
ms | milliseconds |
s | seconds |
m | minutes |
h | hours |
d | days |
These time constants can be used when building formulas and and functions as well as when using filters in order to manipulate time/date data.
Examples of time/date constants
Anchor function function
Example of Date/Time constants within functions
function | |
function |
*Today is October 10, 2012
Function(s) | Result | Function with constant | Result |
---|---|---|---|
TODAY() | Oct 10, 2012 12:00:00 AM | TODAY()-5h | Oct 09, 2012 07:00:00 PM |
TODAY() | Oct 10, 2012 12:00:00 AM | TODAY()+8h+5m | Oct 10, 2012 08:05:00 AM |
TIMESTAMP() | 1,349,820,000,000 | TIMESTAMP()+2d | 1,349,992,800,000 |
TIMESTAMP() | 1,349,820,000,000 | TIMESTAMP()+45d+12h | 1,353,751,200,000 |
Anchor | ||||
---|---|---|---|---|
|
Original data:
- Apply a filter and choose Advanced.
- Type in the filter you want to apply including time constant if needed. For example, #UserDates!Time >= ASDATE("01-05-2012";"MM-dd-yyyy")+5d.
- View the data on the filtered sheet.
...
- Open a calculation workbook sheet and create your formula, for example: (#UserDates!Time) <= ASDATE("01-05-2012";"MM-dd-yyyy")+48h || (#UserDates!Time) >= ASDATE("01-20-2012";"MM-dd-yyyy")-48h.
- View the formula results.
Modifying Date/Time
Modify values with a data date type using the ADDTODATE function. This function allows for both adding and subtracting from dates and times and takes daylights savings time and leap years into account.
...