Convert excel date in CSV/TXT presented as a number

Comments

1 comment

  • Avatar
    Brian Bontrager

    Excel's "Serial Number" for a date is the number of days since a point in history.   Jan 1, 1900 is day 1

     

    If your date is in a column named csvDate, you can use AddDays(CreateDate(1900,1,1),[csvDate]-1) as the formula for a custom Date-Time column.  Note that the call to AddDays needs to adjust the result by -1 day since 1/1/1900 is day 1 rather than day 0.

     

    However, there seems to be a quirk with AddDays when you are adding large numbers of days. Somewhere above 24000 it wraps around and returns dates in the1800s.

    To work around that we have to modify our forumla to AddDays(AddDays(CreateDate(1900,1,1),24000-1),csvDate-24000-1).  This should work until sometime in 2033.

     

    While this works, someone from Sisense may have a better solution.

     

    1
    Comment actions Permalink

Please sign in to leave a comment.