Separating one DateTime column into a Date and a Time Column

(0) Share

Report

Posted on by Laage

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

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Verified answer

Daniel_Pa 244 on at

Like (1)

Report
Copy link

Link copied!

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).
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.

Was this reply helpful? Yes No
Laage 11 on at

Like (0)

Report
Copy link

Link copied!

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:
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

Was this reply helpful? Yes No
Daniel_Pa 244 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Laage 11 on at

Like (0)

Report
Copy link

Link copied!

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:

Into this:
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

Was this reply helpful? Yes No
Daniel_Pa 244 on at

Like (0)

Report
Copy link

Link copied!

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
Actions
After

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.

Was this reply helpful? Yes No
Laage 11 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Daniel_Pa 244 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No