convert excel serial time format to regular date time format 08-04-2022 10:11 AM

[juresti]

Title: Convert Excel serial time

Description: converts excel time into readable date time format that you can then write to a destination, or for any other process.

Detailed Instructions: This is a simple flow and it should simply plug and play. 

You can change the trigger for your needs.

The only step that requires attention is the step where you specify your time column to split.

Be sure to process your excel rows within the apply to each to be able to access the date time for each row.

The comments within the flow actions should explain the entire flow.

Questions: If you need help integrating this, post a question here.

Anything else we should know: There is a solution somewhere in the community that I have seen before.

This flow however will convert the entire date time and includes hours, minutes, and seconds.

If you normally work with excel files that are exported from other systems, most likely you run across the excel serial time format and this flow should be very useful for that.

[juresti]

csv file exports that can also have this time format.

These can easily be saved as excel for processing with this flow.

[HeatherMStacco]

Sorry I'm new to the community.  How do I see the flow in action?  Do I need to download it to a specific location to have it show up in my flow list?

Thanks for helping a newbie out.

Heather

[juresti]

Hello,

You need to download it and then upload - import it to your power automate.

Once you have it there, you will need to choose a file to process then process your date time column within the formula of the flow.

Enter your column here that needs conversion. My column is jssa_date.

This is the split time action within the apply to each.

Here I have failed to set the add minutes and converted time 2 into a variable, that way when you select the variable it will be either of the two. 

So you need to add a set variable - a new variable, the same one on both sides of the tree and assign it converted time 2 output on that side and add minutes output on the other side.

Be sure to initialize your variable as string, below one of the other variables in the beginning of the flow.

 

Then where I circled in the image below you can have your write action to write the excel data.

You will choose dynamic data from excel list and for your time column choose the new variable that will have either converted time 2 or add minutes outputs.

[HeatherMStacco]

I think I've got it but if I have multiple dates from the excel file that I need to do this with do I run 2 parallel steps?  Won't they end up with the same name of varConvertedTime?  or do I initialize another set of vars and just keep waterfalling with the new vars using the same steps until I've converted them all with different names?

Sorry for all the questions.

[juresti]

Hello,

You would need to make a parallel action and process a second date / time field. You could make as many parallels needed unless there is a limit.

Here is an example.

Make an action to take up the first spot that way you can parallel from there, then make a copy of the actions.

Create this action step filler after split time then you can drag split time below it.

You will need a second date time variable to hold the value of the second field. Of course your second parallel will reference the second variable and second column.

At the bottom of both parallels you will now have two calculated date time variables to choose from along with the excel fields.

By default your action after the parallels should run when both sides are successful allowing you to select from both.

[frsbdg]

I'm curious - why did you use "1899-12-30" as the timestamp for the "addseconds" function instead of "1899-12-31"?

[juresti]

Hello @frsbdg 

At this time I only recall that it was not calculating correctly for me while using 1899-12-31 so I had to use 1899-12-30.

I believe I was getting a result that was off by at least half a day. It could be my other side of the formula is causing me to use 1899-12-30.

[frsbdg]

Thanks, @juresti . After posting my question, I tried different combinations for the timestamp, and it turns out that 1899-12-30 does produce the correct result that matches the Excel serial date. So even though I may not understand why, I'm glad to have found a solution.

Have a great day!