How can I convert an Excel date code into a normal M/D/Y formatted date?

HowDoIDomo
HowDoIDomo Contributor
edited December 2022 in Beast Mode

Hi All,

I have some data that I link to DOMO via the Google Analytics connector. It's provided by a 3rd party and contains dates in Excel codes in their 4** format.


Is there a recommended Beast Mode or other way to fix the date formatting? Due to IT policy, I can't access google sheets to change it manually. Plus, I would have to do that every time the consultant makes an update.


So, I was wondering if anyone knows of a Best Mode formula or Magic ETL tile that could automate that process for me until I can meet with the consultant about the formatting.


Thanks in Advance!

Tagged:

Best Answer

Answers

  • DashboardDude
    DashboardDude Coach
    Answer ✓

    Hi @HowDoIDomo ,


    So the start of that 4** date is Jan 1, 1900 so you need to create a column in an ETL with that Date and then add your number of days to that date. I made a video for you to see:

    https://www.loom.com/share/aa615abfec9645218b42ba8a78ad80be


    John Le

    You're only one dashboard away. 

    More video solutions at: https://www.dashboarddudes.com/pantry

    Circle.png

    John Le

    Let me make you the me of your organization for Domo

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • HowDoIDomo
    HowDoIDomo Contributor

    Hi John @DashboardDude ,


    Thank you so much! That did the trick. Plus your tutorial was very easy to follow.


    I've already bookmarked your web page for the next time I need help.


    Thanks,


    Carlos AKA HowDoIDomo?

  • DashboardDude
    DashboardDude Coach

    @HowDoIDomo ,


    Happy to help!

    Circle.png

    John Le

    Let me make you the me of your organization for Domo

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • froghunter
    froghunter Member

    As always, @DashboardDude has made an awesome tutorial. I've made a small modification that might be useful once I've figured out a small discrepancy.

    My datetime column from Excel has decimal values indicating a part of a day. Ex: 43655.9497453704. This means that it can't be used in the date tile that follows the formula. I tried changing the formula to CAST('1900-01-01 00:00:00' AS DATETIME) without success. So, I then removed the date tile and made this my formula:

    CAST('1900-01-01 00:00:00' AS DATETIME)+(`my_excel_datetime`*60*60*24)

    This works well with one curious exception: The results are exactly 2 days off from the value produced in Excel by converting the column to date format. So, I made this my Domo formula:

    CAST('1900-01-01 00:00:00' AS DATETIME)+((`my_excel_datetime`-2)*60*60*24)

    This works until between about 1930 and about 2030. I need to explore a little more, but for now I have a working solution, and about 7 years to figure out the rest of it. :-)

  • LPaolino
    LPaolino Member

    Hi

    Based on @DashboardDude and using only "add formula" function, I could achieve a good result with:

    DATE_ADD(DATE('1899-12-30'), INTERVAL Column Date DAY)

    Hope it helps :)

Categories