SawtoothTrade
  • Home
  • Examples
  • Tips
  • Free Stuff
  • Links
  • Blog
  • Services
  • Contact
Picture
 SawtoothTrade

Picture

Understanding spreadsheet DateTime formats

The Serial DateTime format of Sierra Chart’s spreadsheets uses a number, an integer and decimal, to designate the date and time, where the integer is the date starting at 1900/1/1 and the decimal is the portion of the day from midnight. For example:

 2013/04/30 00:00:00 equals 41394.00
2013/04/30 06:00:00 equals 41394.25
2013/04/30 12:00:00 equals 41394.50
2013/04/30 18:00:00 equals 41394.75
 There are 41394 days since 1900/1/1 and fractions of the 24 hour day are represented by the decimal.

You can therefore calculate these values:
1 hour = 1/24 = 0.04166666667 because there are 24 hours in a day
1 minute = 1/1440 = 0.00069444444 because there are 1440 minutes in a day
1 second = 1/86400 = 0.00001157407 because there are 86400 seconds in a day

 More examples:
3 Minutes = 3/1440 = 0.002083333
60 seconds = 60/86400 = 0.00069444444
09:30:00 = 0.3958333333
23:59:59 = 0.9999884259
December 12, 2012 = 41255

 You can use the DATEVALUE and TIMEVALUE functions on the spreadsheet to convert the Date or Time in text to the Date or Time format:
=DATEVALUE(“2012/12/12″) = 41255
=TIMEVALUE(“09:30:00″) = 0.3958333333
=TIMEVALUE(“00:00:01″) = 0.00001157407 
  • Home
  • Examples
  • Tips
  • Free Stuff
  • Links
  • Blog
  • Services
  • Contact