How can I convert an Excel date code into a normal M/D/Y formatted date?
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!
Best 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:
John Le
You're only one dashboard away.
More video solutions at: https://www.dashboarddudes.com/pantry
John Le
You're only one dashboard away.
Click here for more video solutions:
3
Answers
-
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:
John Le
You're only one dashboard away.
More video solutions at: https://www.dashboarddudes.com/pantry
John Le
You're only one dashboard away.
Click here for more video solutions:
3 -
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?
1 -
John Le
You're only one dashboard away.
Click here for more video solutions:
1 -
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. :-)
0 -
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 :)
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive