Excel date/time integer

(1) Share

Report

Posted on by CAlvy

215

I have a flow which gets data from an Excel table and either updates the SharePoint list or creates new rows.

As the dates are always a problem, I created variables for each date and the formula for these were:

if(isInt(items('DateColumn']), addDays('1899-12-30', int(items('Apply_to_each_2')?['column']), 'yyyy-MM-ddTHH:mm:ss.fffffffK'), items('Apply_to_each_2')?['column'])

Then in the Update Item for the SharePoint list I had for each date:

if(empty(outputs('Compose_date_variable')), null, outputs('Compose_date_variable'))

This worked absolutely fine if the date came through as an integer like this 45586, or as an actual date (as it sometimes could) or if it was blank.

The problem is I'm using this flow with another Excel table and some dates are coming through with a date and time. Even if we format them in the Excel table as custom dd-MM-yyyy or short date, the time is retained and outputs to PA like this: "45587.3281621875".

For some reason, this isn't recognised as an integer and so the variable is set to this same number with decimal points. If it was an integer I could use an addSeconds formula instead of addDays but instead I think I need a condition.

Has anyone got any ideas how I can do this in the most uncomplicated way? Attaching an image of part of my current flow to get an idea.

Categories:

Business Process Flows

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

trice602 14,777 Super User 2025 Season 2 on at

Like (1)

Report

Excel date/time integer

Hi,

Can you try and convert your date with the time to an integer first in a compose?

INT(45587.3281621875)

------------------------------------------------

⭐ If this was helpful, please like and/or mark as a verified answer to help others find this too!

Always glad to help! 💯💯💯💯💯

Tom

Follow me on LinkedIn - Thomas Rice, PMP | LinkedIn

Was this reply helpful? Yes No
Suggested answer

CAlvy 215 on at

Like (1)

Report
Excel date/time integer

Thanks for the idea @trice602. Actually I decided to use a condition to check if the date output had a decimal place. I then replaced my 'Compose' with a 'Variable' I could use in both my true and false.

If true, I used the formula

addseconds('1899-12-30', int(formatNumber(mul(float(item()?['Date']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

This way I don't need to convert to an integer.

In false I used the same IfInt formula I put in my original post.

This seems to have worked.

Was this reply helpful? Yes No
Chriddle 8,339 Super User 2025 Season 2 on at

Like (2)

Report

Excel date/time integer

Simply check with isFloat(), not isInt()

... or even better get the dates from Excel as ISO-8601 date strings instead of this calculation with addDays() (check the connectors advanced options)

Was this reply helpful? Yes No
CAlvy 215 on at

Like (1)

Report

Excel date/time integer

That's useful to know about IsFloat @Chriddle. I could use that in my condition.

I do already have the DateTime format set to ISO 8601 but it never comes through to the outputs. Thanks

Was this reply helpful? Yes No
Chriddle 8,339 Super User 2025 Season 2 on at

Like (1)

Report

Excel date/time integer

Have you formatted the corresponding cells in Excel as dates?
This ISO-8601 transformation always works here ;)

Was this reply helpful? Yes No
CAlvy 215 on at

Like (0)

Report

Excel date/time integer

No, they're custom. The thing is that the data is an export and the user would need to apply all the formatting. I would rather keep the formulas in the flow in case they don't do this when they save the file. But I should suggest they do.

That's good to know it should always transform if a date - thanks for the suggestion.

Was this reply helpful? Yes No
trice602 14,777 Super User 2025 Season 2 on at

Like (0)

Report

Excel date/time integer

Hey there!

I am following up on this post excel date/time integer in this category building flows from 11/04/2024. I know the post is a little older, but it is a common question here so wanted to take a moment and follow-up. I see there have been 6 replies and 1 likes and asking if your question has been answered. If so, please help the community out by completing one or more of the following actions! Thanks in advance!

Easy as 1-2-3 ‼️💯⭐😎🆒🙏

1) Mark a reply as a verified answer - this is essential, and it helps others with the same general question find this thread. ✅

2) You can also mark one of more replies as a verified answer. Please take a moment and give the author credit for volunteering their time. ✌️

3) Like one or more replies. Kudos always greatly appreciated! 👍

Pro tip: if you found your own solution or workaround, feel free to add a reply to your own post and mark it as the solution too! The goal is to get this thread marked with a verified answer by anyone that finds one or more replies helpful!

Again, thanks in advance for reading and responding, always glad to help! Tom 💯💯💯💯💯

Was this reply helpful? Yes No
trice602 14,777 Super User 2025 Season 2 on at

Like (0)

Report

Excel date/time integer

If I can help with anything else please let me know or tag me in a future post, I enjoy reading and answering questions. Always glad to help! Tom 💯💯💯💯💯

Was this reply helpful? Yes No
Verified answer

CAlvy 215 on at

Like (0)

Report
Excel date/time integer

Hi,

My solution was to update the Compose variables where the date and time may come through.

I used this formula:

if(or(isInt(items('Apply_to_each_2')?['Column']), isFloat(items('Apply_to_each_2')?['Column'])), addseconds('1899-12-30', int(formatNumber(mul(float(item()?['Column']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss'), items('Apply_to_each_2')?['Column'])

I used addseconds rather than addDays if the value came through as an integer or a date-time. Otherwise I'm taking the date value.

On update to SharePoint list, I also needed a formula to make sure null values didn't cause a problem. I used this:

if(empty(outputs('Compose')), null, outputs('Compose'))

This approach has been working fine for me.

Was this reply helpful? Yes No