elapse()

Read(2564) Label: new date, year, quarter, month, day,

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-15which 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