Description:
Get the date/time which is a certain time period before or after a given date/time.
Syntax:
|
elapse(t,k) |
|
|
t±k |
Equivalent to elapse(t,±k) |
Note:
The function gets the date that is k time period before or after the start date t. The unit of parameter k is determined by function option; when no option works, the unit is day.
Parameter:
|
t |
A given start date; When this parameter is datetime type, the function returns a string of date format or datetime format; When this parameter is an integer, the function is equivalent to days@o() that returns an integer |
|
k |
An integer expression; the negative value means finding a date k time period before |
Option:
|
@y |
Compute the date which is k years before or after the specified date |
|
@q |
Compute the date which is k quarters before or after the specified date |
|
@m |
Compute the date which is k months before or after the specified date |
|
@e |
When the specified date is the last day of a month, do not adjust the new date to the last day of the month to which it belongs; by default, the new date will be automatically adjusted to the last day of the corresponding month; this option works with @yqm |
|
@s |
Compute the datetime which is k seconds before or after the specified datetime |
|
@ms |
Compute the datetime which is k milliseconds before or after the specified datetime |
Return value:
String or integer
Example:
When parameter t is a date:
|
|
A |
|
|
1 |
2020-02-15 |
|
|
2 |
=elapse(A1,5) |
2020-02-20, which is 5 days later. |
|
3 |
=date(A1)+5 |
Same result as above, which is 5 days later. |
|
4 |
=elapse@y(A1,-1) |
2019-02-15, which is 1 year before. |
|
5 |
=elapse@q(A1,1) |
2020-05-15, which is 1 quarter later. |
|
6 |
=elapse@m(A1,-1) |
2020-01-15，which is one month before. |
When parameter t is a datetime:
|
|
A |
|
|
1 |
2020-02-15 |
|
|
2 |
=elapse@s(datetime(A1),5) |
2020-02-15 00:00:05, which is 5 seconds later. |
|
3 |
=elapse@s("2020-02-15 10:20:30",1) |
2020-02-15 10:20:31, which is 1 second later. |
|
4 |
=elapse@ms("2020-02-15 10:20:30",1000) |
Same result as above, which is 1000 milliseconds later |
|
5 |
=elapse("2020-02-15 10:20:30",-10) |
2020-02-05 10:20:30, which is 10 days before. |
Special date calculation:
|
|
A |
|
|
1 |
2020-02-29 |
|
|
2 |
=elapse@m(A1,1) |
2020-03-31, which is 1 month later; as the given date is the last day of the month, the function returns the last day of the next month. |
|
3 |
=elapse@em(A1,1) |
2020-03-29, which is 1 month later; as @e option works, the result won’t be adjusted to the last day of the next month. |
|
4 |
=elapse@q(A1,1) |
2020-05-31, which is 1 quarter later; as the given date is the last day of the month, the function returns the last day of the next quarter. |
|
5 |
=elapse@qe(A1,1) |
2020-05-29, which is 1 quarter later; as @e option works, the result won’t be adjusted to the last day of the next quarter. |
|
6 |
=elapse@y(A1,1) |
2021-02-28, which is 1 year later; as the given date is the last day of the month, the function returns the last day of the same month one year later if no same date exists. |
When parameter t is an integer:
|
|
A |
|
|
1 |
=elapse(days@o("2020-01-15"),365) |
18831 |
|
2 |
=date@o(A1) |
2019-01-15 |
|
3 |
=elapse@y(18831,1) |
19231 |
|
4 |
=date@o(A2) |
2020-01-31 |