Age in Power Query different in Excel

[lezawang]

Hi
I tried to use Power Query to find age from DOB. When I click on Get data from Table--> Power Query editor open and when I inserted clicked on Add Column-->Age, I got 109.22:31:27.1589177 and when I Added another column -->Total year, I got 0.31201394

When I "Load and Close" to excel. Age and Total years changed as below:

my questions:

1) How to read this Age 109.22:31:27.1589177

Is it 109 days and 22:31:27 seconds?

2) Why when I Load that value (Age) to Excel, I got different one --> 18.22:31:26 ?
Total years are almost the same between Power Query and Excel

Book1 (1).xlsx
	A	B	C	D	E	F
1	dob			dob	Age	Total Years
2	06/03/2021			06/03/2021 0:00	18.22:31:26	0.301201348
3
Sheet1

 

[Alex Blakenburg]

1) How to read this Age 109.22:31:27.1589177

days.hh:mm:ss.fraction_of_second

2) Why when I Load that value (Age) to Excel, I got different one --> 18.22:31:26 ?
Total years are almost the same between Power Query and Excel

PQ returns it to Excel with an Excel Custom format it thinks is equivalent to the PQ format being
d.hh:mm:ss
Unfortunately in Excel d means the day of the month and in your case it uses 109 days starting at 0/01/1900 which gives 18/04/1900 and d is returning 18.

Since the result is a duration none of the date time formats are really useful so just format it as General or a number and you will get something like 109.938492173898 being duration in days.

 

[lezawang]

Thank you very much.

 

[Alex Blakenburg]

No worries. Happy to help.