cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenDonahue
Skilled Sharer
Skilled Sharer

More issues with dates and Excel

In scouring the forums for help converting an Excel date (5-digit date) to a usable date, I found lots of people who had issues with this. Some had solutions, but they were embedded in loops. I need a single use expression for use in an email.

 

I finally got the expression to work, but it is adding a day to date. I believe this might be because int is rounding up because I am calculating this late in the day...?

addDays('1899-12-31',int(body('Update_a_row')?['Audit-Due-Date']), 'D')

 

so I tried to float it

addDays('1899-12-31',float(body('Update_a_row')?['Audit-Due-Date']), 'D')

 

of course, addDays needs an integer for its second argument, so:

addDays('1899-12-31',div(float(body('Update_a_row')?['Audit-Due-Date'])), 'D')

 

which gives a whole number, cast as an float, so:

addDays('1899-12-31',int(div(float(body('Update_a_row')?['Audit-Due-Date']),1)), 'D')

 

Which gives the same wrong date, with one day added. I can subtract a day, but I believe that will give the wrong day by one under if the entry is made in the morning.

 

I need to take the number that Excel gives me, 44056, and convert it reliably to a same certain date, no matter what time of day that entry is made.

 

Your help is greatly appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions

@BenDonahue 

Hi there.  Is it possible that it's doing the date math correctly, but the final date is in the wrong (possibly UTC) timezone?

 

See if the time is off by the same amount of hours difference you are from UTC, and if so, you can wrap everything in a convertFromUTC() to shift it over.

 

Keep us posted.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

View solution in original post

v-alzhan-msft
Community Support
Community Support

Hi @BenDonahue ,

 

Please take a try with expression below:

addDays('1899-12-30',int(body('Update_a_row')?['Audit-Due-Date']), 'D')

 

Best Regards,

Alice

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3

@BenDonahue 

Hi there.  Is it possible that it's doing the date math correctly, but the final date is in the wrong (possibly UTC) timezone?

 

See if the time is off by the same amount of hours difference you are from UTC, and if so, you can wrap everything in a convertFromUTC() to shift it over.

 

Keep us posted.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

View solution in original post

v-alzhan-msft
Community Support
Community Support

Hi @BenDonahue ,

 

Please take a try with expression below:

addDays('1899-12-30',int(body('Update_a_row')?['Audit-Due-Date']), 'D')

 

Best Regards,

Alice

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Why is the correct day to start on _2_ days before the end of the year, on 12/30 instead of 12/31? Does it have something to do with computers starting counting at 0 or something like that?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,955)