|
Size: 1198
Comment:
|
← Revision 3 as of 2025-04-08 16:19:37 ⇥
Size: 1402
Comment: Cleanup
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 2: | Line 2: |
Excel offers these '''date''' and '''time functions'''. |
|
| Line 9: | Line 11: |
| == Extraction Functions == | == Construction == |
| Line 11: | Line 13: |
| To extract the parts of a date/time, use one of the '''YEAR''', '''MONTH''', '''DAY''', '''HOUR''', '''MINUTE''', or '''SECOND''' functions. | A date is constructed with the '''`DATE`''' function, taking (in order) a numeric year, month, and date. |
| Line 13: | Line 15: |
| ---- == Construction Functions == === DATE === Construct a date value. |
A time is constructed with the '''`TIME`''' function, taking (in order) numeric hours, minutes, and seconds. |
| Line 35: | Line 27: |
| === TIME === | === DATEVALUE and TIMEVALUE === |
| Line 37: | Line 29: |
| Construct a time value. {{{ =TIME(HOUR(B4), MINUTE(B4), SECOND(B4)) }}} |
Excel tries to convert strings that look like dates/times when they are entered. This functionality is exposed in the '''`DATEVALUE`''' and '''`TIMEVALUE`''' functions. |
| Line 45: | Line 33: |
| === DATEVALUE and TIMEVALUE === | === TODAY and NOW === |
| Line 47: | Line 35: |
| Excel tries to convert strings that look like dates/times when they are entered. This functionality is exposed in the '''DATEVALUE''' and '''TIMEVALUE''' functions. === TODAY, NOW === '''TODAY''' exposes the current system date and '''NOW''' exposes the current system datetime. |
'''`TODAY`''' exposes the current system date and '''`NOW`''' exposes the current system datetime. |
| Line 61: | Line 41: |
| == Date Difference Functions == | == Extraction == Given a date or time, to extract a component of it, try one of the '''`YEAR`''', '''`MONTH`''', '''`DAY`''', '''`HOUR`''', '''`MINUTE`''', or '''`SECOND`''' functions. ---- == Durations == |
| Line 65: | Line 55: |
| TODO: code example |
|
| Line 67: | Line 61: |
| TODO: code example | |
| Line 71: | Line 66: |
| CategoryRicottone | CategoryRicottone CategoryTodoCodeExample |
Excel Date/Time Functions
Excel offers these date and time functions.
Contents
Construction
A date is constructed with the DATE function, taking (in order) a numeric year, month, and date.
A time is constructed with the TIME function, taking (in order) numeric hours, minutes, and seconds.
A common pattern to guard against over-specified dates is to extract the necessary components from a date/time and construct the minimal value.
=DATE(YEAR(B4), MONTH(B4), DAY(B4))
This would convert 1/1/2020 12:30:00 PM (which cannot be logically compared to a date) into 1/1/2020 (which can).
DATEVALUE and TIMEVALUE
Excel tries to convert strings that look like dates/times when they are entered. This functionality is exposed in the DATEVALUE and TIMEVALUE functions.
TODAY and NOW
TODAY exposes the current system date and NOW exposes the current system datetime.
Extraction
Given a date or time, to extract a component of it, try one of the YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND functions.
Durations
DATEDIF
TODO: code example
DAYS
TODO: code example