cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beanybone
Helper I
Helper I

Date and Time formatting

Hi Everyone, I'm having an issue with dates in my flow. 

 

I am referencing a date in an excel spreadsheet that is populated from a form. As it's populated from a form it pulls date and time. 

 

My flow looks for a date in a spreadsheet and if it is 21 days or older it deletes a row. I have 3 compose functions as follows;

 

Compose 1 - 'Date completed' taken from spreadsheet

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

Compose 3 - formatDateTime(addDays(utcNow(), -21),'yyyy-MM-dd')

 

Compose 2 is failing as compose 1 is pulling 44174.389537037 instead of just 44174 for example.

 

without manually updating the date in the spreadsheet I can't get it to just pull date and I can't find a setting in the forms to only record date, not time. 

 

Can you help? 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@beanybone Yep.. That was I had suggested.. My Variable was timeround yours is Compose output which hold the date variable 🙂

 

int(first(split(string(<Date Completed>)),'.')))

 

I am glad that the problem has resolved.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

10 REPLIES 10
CFernandes
Super User
Super User

Hey MAte @beanybone 

 

CFernandes_0-1618566387803.png

 

int(first(split(string(variables('timeround')),'.')))
 
CFernandes_1-1618566421069.png

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

hey, thanks. where in my flow do I add this compose? 

Hello @beanybone 

 

CFernandes_0-1618567431617.png

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Sorry you lost me, this is how my flow is set up, when I try to insert the above I just get errors

 

beanybone_1-1618568195172.png

 

Hey @beanybone 

 

Try to replace the action in screenshot below with 

 

CFernandes_0-1618569255731.png

 

int(first(split(string(<Date Completed>)),'.')))

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

 

Afraid not, I get an error to say the expression is invalid.

OK!! What is the error? What the Datatype returned by your Date Completed?

 

Perhaps it returns a number, you can use the Format number action to Format any number.

 

CFernandes_0-1618577591098.png

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

 

I don't get that far it won't let me enter the expression, when I type it in and press ok, it pops up that the expression is invalid? 

I managed to figure it out, it might be a little clunky but ... 

 

instead of replacing the initial 'date completed' compose as you suggested, I added another with the following

int(first(split(string(outputs('Compose')),'.')))

and seems to have done the trick! 

@beanybone Yep.. That was I had suggested.. My Variable was timeround yours is Compose output which hold the date variable 🙂

 

int(first(split(string(<Date Completed>)),'.')))

 

I am glad that the problem has resolved.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

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 (1,797)