Value produced by `decimal-date-time`?

The docs for decimal-date-date() note (emphasis mine) that it:

Converts dateTime value to the number of days since January 1, 1970 UTC. This is the format used by Excel.

I tested this survey:

type name label calculation
calculate calc decimal-date-time(today())
note mycalc ${calc}

And the note displayed the number 17821.166666666668 but if you plug that number into Excel and change the cell format to a date, it displays as 10/15/1948. The documentation is wrong? I'm not interpreting the documentation correctly? Something else?

1 Like

I believe the documentation is probably incorrect - Excel dates are from 1900, not 1970:

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time....
Excel can also treat the date portion of a serial date as the number of days since 1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for compatibility with the Macintosh system.

So 10/15/1948 = 01/01/1900 plus 1781 days sounds about right... Probably just need to amend the ODK documentation accordingly.

Whereas Jan 01 1970 is the epoch date for Unix-based computers (ie a real OS... :wink: )


Hmm, that would be my bad! I think it's helpful for users to know it's the format used by Excel but off by 70 years but maybe that doesn't need to be in the spec at all. I think I meant it as a reminder of why we do the fractional day piece (which should convert to a time if you make the cell a dateTime).

We have some documentation about doing Excel-based conversions in the context of the enumerator log: Perhaps we can have something similar in the user-facing docs for decimal-date-time() and drop any mention of Excel in the spec.

1 Like

And in case you are wondering why "1904? huh? WTF!", here's a bit o' computer history explaining why:

The original Mac development team chose midnight, January 1, 1904, as the start of the Mac calendar in part because it's mathematically convenient to have a calendar system start on a leap year, which 1900 was not.

Everything has a reason, no matter how obscure :slight_smile:

ah, 70 years off, who cares... Indeed I believe it was Mr ISO8601 himself (aka @ggalmazor) who said "consistency is far more important that correctness" :stuck_out_tongue_winking_eye:

What in the name of what?

Everyone is wrong! Let's fix it!


Every time I read stuff like this one about dates and times, my love for ISO8601 grows.

1 Like

We all have full confidence that you will come up with the right solution, @ggalmazor!

(which is code for "we're all sure glad we dont have to sort out this mess..." :sunglasses: )

It's super easy: y'all just need to do what I say and never question anything :rofl:

Now, regarding @danbjoseph's OP, I think we should remove the excel part of that explanation because it's misleading, right?

1 Like

removal here
can edit to add the 70 years off bit instead of just cutting the sentence if y'all think that would be better.

1 Like