cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laage
Regular Visitor

Separating one DateTime column into a Date and a Time Column

Hi, 

I'm working on a flow in PAD to convert a CSV to a more human readable Excel-sheet.

I'm mostly done, but I'm getting stuck on converting a DateTime column (example data: 2021-05-19T16:17:59.2758102Z) into two separate columns; Date (example: 2021-05-19) and Time (example: 16:17:59.2758102Z - though preferable would be just: 16:17).

For my manual work with this I have two simple formulas for splitting the DateTime at the 'T' character.

Date: =LEFT(A2; SEARCH("T";A2;1)-1)

Time: =RIGHT(A2;LENGTH(A2)-SEARCH("T";A2;1))

 

However I'm not sure how to implement this in PAD.

My thought was inserting two new rows in the table and use the formulas to calculate the values, but I'm not sure how to go about that in PAD. Secondly I would prefer to delete the original DateTime column before handing the sheet over, but I would need to convert the calculated rows to values only before doing that I'm guessing.

Any hints, solutions or suggestions are most welcome.

 

Kind regards

Laage

1 ACCEPTED SOLUTION

Accepted Solutions
Daniel_Pa
Resolver I
Resolver I

Hi Laage

 

I've tried using the datetime you provided, and just threw it in a Set Variable - using a Split text, with space as the delimiter, i got a list containing date and time in two separate values.

You could try something similar, and just write them directly to your Excel file.

You might need to format them afterwards (you should be able to use "Convert text to datetime" and specify a custom format).

datetime.png

When writing them to Excel you can use the format %VariableName[index]%, or in this case %DateTimeList[0]% for date and %DateTimeList[1]% for time.

View solution in original post

7 REPLIES 7
Daniel_Pa
Resolver I
Resolver I

Hi Laage

 

I've tried using the datetime you provided, and just threw it in a Set Variable - using a Split text, with space as the delimiter, i got a list containing date and time in two separate values.

You could try something similar, and just write them directly to your Excel file.

You might need to format them afterwards (you should be able to use "Convert text to datetime" and specify a custom format).

datetime.png

When writing them to Excel you can use the format %VariableName[index]%, or in this case %DateTimeList[0]% for date and %DateTimeList[1]% for time.

View solution in original post

Laage
Regular Visitor

Thank you!
Was so focused on the Excel/formula-based solution I didn't even think about manipulating the variables directly.

I went with the Split Variable first, but on closer consideration decided that I probably didn't need it as the timestamp itself could just be converted from text to datetime and from there I could just do a custom convert datetime to text; one into the date and one into the short time:

Laage_0-1626696767222.png

Worked perfectly!

 

You don't happen to know if there's a simple way in PAD to turn the final result into a table?

 

Kind regards

Laage

Hi Laage

 

Good to hear you figured it out! And your solution is much prettier. I just couldn't get it to work (might be localization setting regarding datetime format).

 

If you are asking for a way to store several variables in a table variable, I don't think you are able to do so in PAD. You could try using a csv-format to store it, but I'd just suggest saving it in an Excel file as the easiest solution.

 

If you could elaborate on the end goal I'd be glad to suggest possible solutions.

Laage
Regular Visitor

I believe I'm done with the data manipulation for the flow, now it's about the final presentation. 😁

 

It's not essential, but the people who are supposed to get the finished reports are probably not Excel whizzes.

So I thought that if I could prettify the sheets as part of the flow I'd do that

 

Basically turning this:

Laage_0-1626699386877.png

 

Into this:

Laage_1-1626699462907.png

Manually in Excel it's just a matter of selecting the active cells and go via Insert > Table (I'm working from a Swedish language version of Excel so I might be slightly off, but I'm guessing not by much).

I was hoping there was an easy way of doing this in PAD. If not I might just let it go.

 

Kind regards

Laage

You could achieve it through hotkeys - if they are similar to mine (using Ctrl+Home to ensure the top left cell is active, when transforming the content to a table):

Before

before.png

Actions

table.png

After

after.png

 

Although this does not help adjusting the width of the cells. If you need more advanced formatting, I'd recommend using VBA, if you are familiar with it?

 

Edit: Actually, there is also a hotkey for AutoFit for cell width: Alt-H-O-I, sequentially. Combined with preceding Ctrl+A, you should be able to make your table Auto-beautifully-Fit the content.

Laage
Regular Visitor

Again, thank you. I keep learning cool stuff.

I haven't touched VBA in ages, I'm primarily Powershell now.

I don't think I'll bother with Cell-width, this last step was primarily for my own benefit.

/Laage

 

N.B.

In spite of being Danish I'd never realized that dronning Margrethe had an Icelandic middle name containing the letter thorn... That's pretty cool.

Glad to help. 🙂

 

Actually thought you were Swedish, since you were working on a Swedish language version. I'll admit as a fellow Dane, i wasn't aware either - just thought I'd sneak in a small joke and learned something new myself.

 

Best of luck with PAD and feel free to tag me another time, if you need assistance.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,453)