Answered

Stop PA coverting Excel Time to DateTime and Keep as Text Value

(0) Share

Report

Posted on by KurtisRC

Hello All,

I have searched everywhere and unable to solve the issue I am facing. I am trying to automate a Stats card that I send to my Analyst monthly to show their stats for last month. This will include like Total tickets resolved, how many phone calls they took, average time they spent on the phone.

The issue I'm facing is that it is converting their "total times" into dateTime format, so instead so saying they have spoken a total of 23 hours on the phone it is showing in time format. How can I get PA to just read the number as text value without it automatically converting it into DateTime.

Excel Spreadsheet

How it is outputting it

I don't want to have to use formatDateTime because this is causing issues if their stat is over 24 hours, as you can see from the image below it treats it as a new day. And I can't do 12 hours as it will convert 18:00:00 to 6pm.

I can't change Excel to format the cells in "Text" because then this will change the value and it will become "1.01732638888889".

Is there anyway of doing this??

I appreciate any help you can offer.

Categories:

Building flows

All responses (4)

Answers (1)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @GOSHKRC ,

Please try:

formatDateTime(YourDataTimeColumn,'HH:mm:ss')

Best Regards,
Bof
KurtisRC 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Unfortunately I can't use FormatDateTime in either 24 or 12 hours. As detailed above if a field has value that is over 24 hours, so let's say 34:40:00 it will treat this as a new day and display it as 10:40:00.
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @GOSHKRC ,

Please try:

concat( split(string(mul(float(items('Apply_to_each')?['Admin']),24)),'.')[0], ':', mod(int(split(string(mul(float(items('Apply_to_each')?['Admin']),mul(24,60))),'.')[0]),60), ':', mod(int(split(string(mul(float(items('Apply_to_each')?['Admin']),mul(24,3600))),'.')[0]),60) )

Result:

Best Regards,

Bof
KurtisRC 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Amazing, that worked perfectly.
I would have never of worked that out so I appreciate your help with this.