Skip to main content
Power Automate
    • Connectors
    • Templates
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process advisor
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Advisor
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • Re: convert excel serial time format to regular da...
    Accepted Solution

    Re: convert excel serial time format to regular date time format

    08-04-2022 10:11 AM

    frsbdg
    Frequent Visitor
    49 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    convert excel serial time format to regular date time format

    ‎02-11-2020 07:14 AM

    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.

     

    Preview file
    1 KB
    excelserialtimeconversion_20200211143847.zip
    excelserialtimeconversion_20200211143847.zip
    Labels:
    • Labels:
    • Solutions
    Message 1 of 9
    7,696 Views
    1 Kudo
    Reply
    • All posts
    • Previous Topic
    • Next Topic
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎02-17-2020 07:44 AM

    csv file exports that can also have this time format.

     

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

    Message 2 of 9
    7,653 Views
    0 Kudos
    Reply
    HeatherMStacco
    HeatherMStacco
    New Member
    In response to juresti
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-06-2020 01:40 PM

    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

    Message 3 of 9
    2,056 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to HeatherMStacco
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-07-2020 09:16 AM

    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.

     

    11.PNG

     

    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.

    14.PNG13.PNG 

     

    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.

     

    12.PNG

     

     

    Message 4 of 9
    2,042 Views
    1 Kudo
    Reply
    HeatherMStacco
    HeatherMStacco
    New Member
    In response to juresti
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-07-2020 02:28 PM

    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.

    Message 5 of 9
    2,029 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to HeatherMStacco
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-08-2020 07:41 AM

    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.

     

    16.PNG

     

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

    17.PNG

     

    Message 6 of 9
    2,014 Views
    1 Kudo
    Reply
    frsbdg
    frsbdg
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Thursday

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

    Message 7 of 9
    49 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to frsbdg
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    10 hours ago

    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.

     

     

    Message 8 of 9
    4 Views
    0 Kudos
    Reply
    frsbdg
    frsbdg
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    9 hours ago

    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!

    Message 9 of 9
    3 Views
    1 Kudo
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2022 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions