This chapter lists code examples about handling datetime, including Get the current date and time, Get different parts of a datetime value, Compose a datetime/date/time value, The datetime which is a certain time period before/later, Interval between two datetimes, Find what day a date is, The first and last day of a week/month/quarter, The number of days in a month/quarter/year, Generate a datetime sequence by fixed intervals, Get the second and last Fridays in a month/quarter/year and the total number of Fridays in this period, Check if it is date, Get age and Work days calculations.
|
|
A |
|
|
1 |
=now() |
|
|
2 |
=now@d() |
Return a date type value |
|
3 |
=now@m() |
Accurate to the minute |
|
|
A |
|
|
1 |
=now() |
|
|
2 |
=year(A1) |
|
|
3 |
=month(A1) |
|
|
4 |
=day(A1) |
|
|
5 |
=time(A1) |
|
|
6 |
=hour(A1) |
|
|
7 |
=minute(A1) |
|
|
8 |
=second(A1) |
|
|
|
A |
B |
C |
D |
E |
F |
|
|
1 |
1989 |
'02 |
'01 |
'02 |
34 |
55 |
|
|
2 |
=string(A1)+"-"+string(B1)+"-"+string(C1)+" "+string(D1)+":"+string(E1)+":"+string(F1) |
|
|
|
|
|
|
|
3 |
=datetime(A2) |
|
|
|
|
|
|
|
4 |
=datetime(A2,"yyyy-MM-dd HH:mm:ss") |
|
|
|
|
|
|
|
5 |
=date(A1,int(B1),int(C1)) |
|
|
|
|
|
|
|
6 |
=time(int(D1),E1,F1) |
|
|
|
|
|
|
|
7 |
=datetime(A1,int(B1),int(C1),int(D1),E1,F1) |
|
|
|
|
|
|
|
8 |
=datetime(A5,A6) |
|
|
|
|
|
|
|
|
A |
|
|
1 |
2006-07-05 |
|
|
2 |
=elapse(A1,5) |
5 days later |
|
3 |
=elapse("1972-11-08 10:20:30",-10) |
10 days before |
|
4 |
=elapse@s(datetime(A1),5) |
5 seconds later |
|
5 |
=elapse@s("1972-11-08 10:20:30",-10) |
10 seconds before |
|
6 |
=elapse@m(A1,-1) |
1 month before |
|
7 |
=elapse@y(A1,-1) |
1 year before |
|
|
A |
B |
|
|
1 |
2010-5-01 23:20:15 |
2010-05-03 01:01:01 |
|
|
2 |
=interval(A1,B1) |
|
The number of days between two datetimes |
|
3 |
=interval@s(A1,B1) |
|
The number of seconds between two datetimes |
|
4 |
=interval@y(A1,"2001-01-01") |
|
The number of years between two dates |
|
5 |
=interval@m(A1,"2001-01-01") |
|
The number of months between two dates |
|
6 |
=interval@ms(A1,now()) |
|
The number of milliseconds between two datetimes |
|
7 |
=interval(A1,B1) |
|
The number of days between two datetimes |
|
8 |
=interval@s(A1,B1) |
|
The number of seconds between two datetimes |
|
|
A |
|
|
1 |
2005-01-08 |
|
|
2 |
=day@w(A1) |
Get what day the date is; 1 stands for "Sunday" |
|
|
A |
|
|
1 |
2006-03-06 |
|
|
2 |
=pdate@w(A1) |
The first day of the week |
|
3 |
=pdate@we(A1) |
The last day of the week |
|
4 |
=pdate@q(A1) |
The first day of the quarter |
|
5 |
=pdate@qe(A1) |
The last day of the quarter |
|
6 |
=pdate@m(A1) |
The first day of the month |
|
7 |
=pdate@me(A1) |
The last day of the month |
|
|
A |
|
|
1 |
2007-08-08 |
|
|
2 |
=days(A1) |
The number of days in the month in A1 |
|
3 |
=days@y(2006) |
The number of days in the year 2006 |
|
4 |
=days@y(A1) |
The number of days in the year in A1 |
|
5 |
=days@q(A1) |
The number of days in the quarter in A1 |
|
|
A |
|
|
1 |
2000-08-10 12:00:00 |
|
|
2 |
=periods@y(A1,now(),1) |
Set 1 year as the interval unit |
|
3 |
=periods@q(A1,now(),1) |
Set 1 quarter as the interval unit |
|
4 |
=periods@m(A1,now(),2) |
Set 2 months as the interval unit |
|
|
A |
|
|
1 |
=now() |
|
|
2 |
=pdate@m(A1) |
The start date of the month in A1 |
|
3 |
=pdate@me(A1) |
The end date of the month in A1 |
|
4 |
=periods(A2, A3,1) |
The sequence of dates between the first day and the last day in the month |
|
5 |
=A4.select(day@w(~)==6) |
The sequence of Fridays in A4 |
|
6 |
|
An alternative method |
|
7 |
=elapse(A2,(d=day@w(A2),if(d==7,6,6-d))) |
Get the first Friday |
|
8 |
=periods@x(A7,A3,7) |
Get the sequence of Fridays, which doesn’t contain the end date of the month got in A3 |
|
9 |
=A8(2) |
Get the second Friday |
|
10 |
=A8.m(-1) |
Get the last Friday |
|
11 |
=A8.len() |
Get the number of Fridays |
|
|
A |
|
|
1 |
=ifdate("2020-04-24") |
false |
|
2 |
=ifdate(date("2020-04-24")) |
true |
|
|
A |
|
|
1 |
1995-3-31 |
|
|
2 |
=now@d() |
2020-03-30 |
|
3 |
=age(A1) |
24, accurate to day |
|
4 |
=age@m(A1) |
25, accurate to month |
|
5 |
=age@y(A1) |
25, accurate to year |
|
|
A |
|
|
1 |
2020-4-24 |
|
|
2 |
2020-5-10 |
|
|
3 |
[2020-4-26,2020-5-1,2020-5-4,2020-5-5,2020-5-9] |
A sequence of dates according to public holidays |
|
4 |
=workday(A1,1) |
2020-04-27, the date one non-week-day after A1’s date |
|
5 |
=workday(A1,1,A3) |
2020-04-26, the date one workday after A1’s date |
|
6 |
=workdays(A1,A2) |
A sequence of non-week-days between A1’s date and A2’s date |
|
7 |
=workdays(A1,A2,A3) |
A sequence of workdays between A1’s date and A2’s date |