Top Page | Upper Page | Contents | About This Site | JAPANESE

Treatment of Time

Time and period are similar. But Time Series Analysis separates to use them clearly.

There are two kinds to treat time data for example "2007/1/1 00:00:00".

20070101000000

Easy to use because width is always same. It is used as the part of the name of file or ID.

But changing into "20061231000000" is difficult.

Difference between " 20070101000000" and " 20061231000000" should be "1" in time analysis, but we cannot calculate the period easily.

The reason is that computers think
"20070101000000" as "20 trillion 70 billion 101 million",
not "January 1st 2007 0 o'clock".

2007/1/1 00:00:00

This pattern is thought as "January 1st 2007 0 o'clock". We can see " / " and " : ", but computers do not deal with as "2007/1/1 00:00:00" in their brain.

Computers treat as " 39083 ".
For example, " 2007/1/1 12:00:00 " is " 39083.5 " , and the next day is " 39084 "
In this pattern " 1 " means "1900/1/1 00:00:00". " 39083 " means "39083 days after 1900/1/1"

Some software cannot deal with the form "2007/1/1 00:00:00"
In this case, we need to transform into "39083".

In this pattern, difference between two numbers is period.
" 1 day " is " 1 ". " 1 hour " is " 0.0416666666 " ( = 1/24 ).

Formulations of Excel

These are examples of Excel formulation.

Formulations of Excel
Before Formulation After
20080120 =DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2)) 2008/01/20
200801201234 =DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+MID(A1,9,2)/24+MID(A1,11,2)/24/60 2008/01/20 12:34
20080120123456 =DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+MID(A1,9,2)/24+MID(A1,11,2)/24/60+MID(A1,13,2)/24/60/60 2008/01/20 12:34:56
2008/01/20 =TEXT(A1,"yyyymmdd") 20080120
2008/01/20 12:34 =TEXT(A1,"yyyymmddhhmm") 200801201234
2008/1/20 12:34:56 =TEXT(A1,"yyyymmddhhmmss") 20080120123456



NEXT Data Physics

Tweet