Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to write an M formula to convert Julian dates using Power Query |
---|
This short blog explains how to convert Julian dates to "normal" ones, and how the formula used actually works! |
In this blog
Imagine that you have an Excel workbook or other data source containing Julian dates:
I've shown the UK-style date alongside, but this is actually what we're trying to derive.
This blog explains the theory of how to convert the dates to a UK or US format, then shows how to do this in Power Query.
You can try this out yourself using this workbook. I got the idea for this from this blog, and it was prompted by a question from James Cartney.
To convert the date type to UK format, here are the steps to follow in Power Query (using the top date, 2017039, as an example):
No. | Step | What it does | What it would give |
---|---|---|---|
1 | Number.RoundDown([JulianDate]/1000) | Gets the year number from a 7-digit Julian date | 2017 |
2 | #date( Step 1 output ),1,1) | Gets the 1st January for this year | 01/01/2017 |
3 | Number.Mod([JulianDate],1000) | Divdes the 7-digit date by 1000, and takes the remainder to give the number of days to add | 39 |
4 | Date.AddDays( Step 1 output , Step 3 output-1) | Adds this many days on to the first of January - less one | 08 Feb 2017 |
You need to subtract 1 because in the Julian date format the first day of January is day one, not zero.
I'm showing this via Excel, but it would work equally well in Power BI Desktop. First choose to import the Excel workbook containing your dates:
Choose to import a workbook.
Choose the file, then choose the sheet within it:
The data in our example is held in a sheet imaginatively called Sheet1.
Now choose to edit how you import this data:
Click on this button at the bottom right.
Now choose to add a column:
Add a custom column to your query.
Create your formula:
You can paste the formula from the box below.
Here's the formula to paste in:
Success!
OK, so I already had a column with the Excel dates in, but ...
My only remaining question is: who uses dates in the Julian format?
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.