cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tofifi
Helper II
Helper II

date and time

Hello

 

I created a flow which takes rows with date and time from excel sheet and sends mail to user. Unfortunately i get message like this:

2021-03-10T00:00:00.000Z at 1899-12-30T09:30:00.000Z

but i want this:

2021-03-10 at 09:30:00

 

Does anyone knows how to do it?

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions

@Tofifi 

 

Please use this one only-

Hardesh15_0-1611894328502.png

Variable - SerialDateTime , type -float.

Expression used-

addSeconds('1899-12-30',int(formatNumber(mul(variables('SerialDateTime'),86400),'0','en-us')),'HH:mm:ss')

output-

Hardesh15_1-1611894465985.png

 

Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!

Regards,
Hardesh

 

 

 

View solution in original post

@Tofifi Okay do one thing. In one compose take Time value and then in set variable pass float(Output('compose')).

Check now.

Thanks

Hardesh

View solution in original post

47 REPLIES 47
Rahber
Solution Sage
Solution Sage

you can use

 

formatDateTime(yourdatevariable,'yyyy-MM-dd') and 
formatDateTime(yourtimevariable,'HH:mm:ss')
 
Make sure to update the variable for your date and time in the above
 
Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,
Rahber
 
To acehive your need
 
 

But how and where add this? I'm still learning building flows so that would mean much to me.

Hi Rahber

 

I figured out how and where add it, but i have problem and message: 

Tofifi_0-1611594036753.png

Do you know how to fix it?

@Tofifi Pls share your flow screen.

For date serial number (in Compose action i am passing excel date). Pls use following expression-

addDays('1899-12-30',int(outputs('Compose')),'dd-MM-yyyy')

Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!

Regards,
Hardesh

Hi @Hardesh15

 

Fortunately i figured out how to fix the date issue but thank you for your reply. Now i have the same problem with time:

Tofifi_0-1611598056213.png

 

time i have in different column.

 

@Tofifi Pls share your flow screen not running instance. i want to check your expression for date and time.

Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!

Regards,
Hardesh

@Hardesh15 
That's how it looks:

Tofifi_0-1611653488971.png

 

@Hardesh15 

Sorry i forgot to put formula, here it is:

Tofifi_0-1611733752695.png

Tofifi_1-1611733778895.png

 

@Tofifi pls check if your date column has null value. If it is null value then use condition isblank(column name) in format date time expression.

Thanks

Hardesh

@Hardesh15  

No, it doesn't have null value:

Tofifi_0-1611777338951.png

 

I don't have problem with date anymore but with time.

Tofifi_1-1611777438123.png

 

@Tofifi  pls use one compose action and pass your excel time column so that i get to know what kind of data is coming from excel.

Thanks

Hardesh

@Hardesh15 

 

i used compose and still got error:

Tofifi_0-1611828030120.png

How do you want me to pass time column?

 

@Tofifi  simply pass excel column Time EST/EDT  from dynamic content tab. Do not use any expression.

Thanks

Hardesh

@Hardesh15 
This is the result:

Tofifi_0-1611841700657.png

 

@Tofifi  it means that int function is rounding of time as zero so null value error is coming. Fetching time from excel is little bit complex. I will share screens after sometime.

Thanks

Hardesh

@Hardesh15 

I will wait for the results.

Thank You!!

 

 

@Tofifi Please follow step by step. ( i have hard coded substring index but it will work)

Hardesh15_0-1611849236272.png

Hardesh15_1-1611849311406.png

Output-

Hardesh15_2-1611849486108.png

 

i did not make calculation for seconds so concatenate in last as 00.

Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!

Regards,
Hardesh

 

@Tofifi  if you face out of index error change 10 to 5. Unfortunately logic is working for 10+ hour time. i will fix and give you new solution.

 

Thanks

Hardesh

Thank you @Hardesh15  for your effort! I will try this out.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,778)