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,"yyyyMMdd 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 
20060705 

2 
=elapse(A1,5) 
5 days later 
3 
=elapse("19721108 10:20:30",10) 
10 days before 
4 
=elapse@s(datetime(A1),5) 
5 seconds later 
5 
=elapse@s("19721108 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 
2010501 23:20:15 
20100503 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,"20010101") 

The number of years between two dates 
5 
=interval@m(A1,"20010101") 

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 
20050108 

2 
=day@w(A1) 
Get what day the date is; 1 stands for "Sunday" 

A 

1 
20060306 

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 
20070808 

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 
20000810 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,6d))) 
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 