Datetime

Read(234) Label: datetime,

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, and Get the second and last Fridays in a month/quarter/year and the total number of Fridays in this period.

Get the current date and time

 

A

 

1

=now()

 

2

=now@d()

Return a date type value

3

=now@m()

Accurate to the minute

 

Get different parts of a datetime value

 

A

 

1

=now()

 

2

=year(A1)

 

3

=month(A1)

 

4

=day(A1)

 

5

=time(A1)

 

6

=hour(A1)

 

7

=minute(A1)

 

8

=second(A1)

 

 

Compose a datetime/date/time value

 

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)

 

 

 

 

 

 

 

The datetime which is a certain time period before/later

 

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

 

Interval between two datetimes

 

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

 

Find what day a date is

 

A

 

1

2005-01-08

 

2

=day@w(A1)

Get what day the date is; 1 stands for "Sunday"

 

The first and last day of a week / month / quarter

 

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

 

The number of days in a month / quarter / a year

 

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

 

Generate a datetime sequence by fixed intervals

 

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

 

Get the second and last Fridays in a month/quarter/year and the total number of Fridays in this period

 

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