Unanswered

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

I have the same question (0)

All responses (4)

Answers (1)

v-bofeng-msft on at

Like (0)

Report

Hi @GOSHKRC ,

Please try:

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

Best Regards,
Bof

Was this reply helpful? Yes No
KurtisRC 21 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (0)

Report
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

Was this reply helpful? Yes No
KurtisRC 21 on at

Like (0)

Report

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

Was this reply helpful? Yes No