cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmfillman
Frequent Visitor

formatDateTime Being Ignored

I have 2 date columns from an Excel file. In the Excel file, the columns are formatted identically as Custom (yyyy-mm-dd hh:mm:ss) and display identically in the file, but "List rows present in a table" (date format set to ISO 8601) is seeing them differently:

 

    "Column1""2022-01-17T08:00:00.000Z",

    "Column2""44578.3333333333",
 

For Column1, formatDateTime works as expected every time and gives me a formatted date to match:

formatDateTime(items('Add_or_Update_Existing_Items')?['Column1'], 'MM/dd/yyyy')

 

For Column2, formatDateTime never works. The function returns 44578.3333333333 or 0:

formatDateTime(items('Add_or_Update_Existing_Items')?['Column2'], 'MM/dd/yyyy')

 

For testing, I have tried the addDays option I've seen in other posts. I've tried 2 variable, setting the first to the 44578.3333333333 and then formatDateTime in the 2nd variable, using the first variable's value. I've tried the Compose option similar to the variables, etc. I never get a formatted date for Column2.

1 ACCEPTED SOLUTION

Accepted Solutions
Kaif_Siddique
Impactful Individual
Impactful Individual

Hi @jmfillman ,

 

Use below expression change to format date:

 

formatDateTime(addseconds('1899-12-30', int(formatnumber(mul(float(44578.3333333333), 86400), '0'))), 'MM/dd/yyyy')

 

Kaif_Siddique_0-1664217451774.png

Let me know if any queries.

 

If you like my response, please give it a Thumbs Up.
If this helps, please Accept as solution.


Regards
Kaif

View solution in original post

2 REPLIES 2
Kaif_Siddique
Impactful Individual
Impactful Individual

Hi @jmfillman ,

 

Use below expression change to format date:

 

formatDateTime(addseconds('1899-12-30', int(formatnumber(mul(float(44578.3333333333), 86400), '0'))), 'MM/dd/yyyy')

 

Kaif_Siddique_0-1664217451774.png

Let me know if any queries.

 

If you like my response, please give it a Thumbs Up.
If this helps, please Accept as solution.


Regards
Kaif

jmfillman
Frequent Visitor

This appears to be working, thank you!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,956)