cancel
Showing results for
Did you mean:
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...?

so I tried to float it

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

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

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
MVP

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.

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.

3 REPLIES 3
MVP

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.

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.

Skilled Sharer

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?

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Community & How To Videos

Check out the new Power Platform Community Connections gallery!

#### 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
Top Kudoed Authors
Users online (2,955)