Answered

I'm an idiot, cant figure out Excel Date Time Conversion

(0) Share

Report

Posted on by P_Molitor

I can't figure out how to convert dates retrieved in a "List_rows_present_in_table" function from serial to a date of any kind. I've found all kinds of answers out there that appear to be successfully answered but they mostly just list a formula that I can't figure out where I am supposed to input in to my flow.

I was even going to be ok with the crazy long ISO "Zulu" time, but I have the date time format on my GET function set to ISO 8601 and when I put it in my HTML table, its still the serialized date. So will someone please tell me what formula I need, and most importantly where to input it in to my flow because everything I have tried causes an error when saving the flow or when running it.

Simple Process.png

Serialized dates.png

Date Change.png

I have the same question (0)

All responses (8)

Answers (1)

Pstork1 68,306 Most Valuable Professional on at

Like (2)

Report
Re: I'm an idiot, cant figure out Excel Date Time Conversion

Here's the formula to translate from a Serial Date to a regular Date/Time

addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['text']),86400),'0’)))

Explanation. A Serial Date is a whole number that represents the number of days and fractional days (time) that have elapsed since midnight 1/1/1900. You need to calculate it based on 12/30/1899 because 1/1/1900 counts as one and 1900 wasn't a leap year so there is no 2/29 that year. That leaves the simplest calculation off by 2. Here's what the formula above does

1) the date (triggerBody()['text']) is converted to a floating point number. (replace that with whatever field holds your serial date from Excel)

2) That number is multiplied by 86,400 which is the number of seconds in a day

3) The result is formatted as a whole number with no decimal places

4) The result is then converted to an integer - This represents the number of seconds in the serial date.

5) The result is then added to the based date of 12/30/1899 and the result is a standard Date/Time.

6) Optional - You can put the whole thing in a formatDateTime() function to convert the Date/Time to a particular format for output.

Was this reply helpful? Yes No
P_Molitor 92 on at

Like (0)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

I much appreciate your time in trying to help me figure this out, unfortunately I think you missed the part of the title where I disclosed that I am an idiot. Your answer is one of the formulas I found in old posts and have been trying to apply to my flow. The problem is, I don't know where to apply it. I thought I could add it to the "DateTime Format" field of the List rows Action as a custom value, but it would not take it. then I tried to apply it to the Create HTML Table action with this as the expression:

formatDateTime(addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['Effective_x003a_']),86400),'0’))))

but I get the error "The Expression is Invalid"

I'm sure your formula works perfectly, I'm just a bit too daft to be able to apply it properly. Ive also tried using this:
addDays('12/30/1899',int(items('Apply_to_each')?['Effective_x003a_']),'yyyy-MM-dd')
and this:
addDays('1899-12-30',int(first(split(item()['Effective_x003a_'],'.'))),'yyyy-MM-dd')
in various ways including but not limited to Compose actions, apply to each actions and HTML Table formulas, but again, I have no Idea what I'm doing.

Was this reply helpful? Yes No
P_Molitor 92 on at

Like (0)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

Without more info on how to use the above formula, I've been trying other things. I put this formula into a "Apply to each";
addDays('1899-12-30', int(item()['Received']), 'MM-dd-yyyy')
After a test run with nothing connecting from it, I found that this does convert the serial dates to MM-dd-yyy format. Unfortunately, as you can see in the image below, it wont let me use the outputs in my HTML table. How do I make this work?

Was this reply helpful? Yes No
P_Molitor 92 on at

Like (0)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

Well, I made 4 posts for help this week, 2 of which I believe have very simple fixes that simply elude a novice such as myself; yet I only got a total of 1 answer. Maybe this forum is geared towards a more advanced user and my questions are just a bother, but things aren't looking good for me trying to learn my way through this stuff enough to really help my Organization.
In case anyone else is wondering how to approach this SERIAL DATE issue, I found that if I don't format my date columns in Excel as 'Date'; but instead format them as 'Text', Excel never changes them to the Serial Code that causes so much headache. I don't know what other issues having dates formatted as 'Text' may cause in the future with my data, but it seems to be the best solution at the moment.
If anyone else is trying this with a document that has been around for a while, you may have issues with this method because when you change the column to 'Text' in your Excel file, it will change all the dates in that column to the Serial Date, so you will have to find a way to convert those numbers to Text dates without reverting back to date format for the column. For my purposes, I've been running my automation tests on a dummy document and when I go live with the process the document will be brand new and formatted just how I want it so this method works well for me. Thanks for the help, and if someone can explain to me how to use the formula @Pstork1 posted or any of the ones I posted in my earlier reply, I'd still like to learn how to use them; or if anyone can think of how having my Date columns formatted as Text in excel might cause issues I'm pen to learning about that too.

Was this reply helpful? Yes No
Verified answer

Pstork1 68,306 Most Valuable Professional on at

Like (1)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

Send me links to the other 3 posts and I'll try to help. There is a lot of traffic on here and it's hard to keep up with all of it.

You are using the formula correctly, but to output it to an HTML table you need to use what you put into the compose 2 in the field for Received in the HTML table action. You don't need the whole Apply to each unless if all you want is an HTML table.

Was this reply helpful? Yes No
jesslynh 179 on at

Like (2)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

I tried to figure this out, but I'm still fairly new at this. Frankly, Microsoft should be embarrassed at how difficult this is. They've offered up Excel as a standard connector, own both products and did not add any basic date conversion for Excel files.

I'm loving Power Automate and the things that it allows me to do, but it makes me take some really deep dives into stuff that you wouldn't think would be required of a "no-code user developer". This, along with other really other, simple, yet basic and missing functionality is sad.

Was this reply helpful? Yes No
P_Molitor 92 on at

Like (1)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

In no way was I trying to imply that you or anyone else were slacking off by not answering my posts I just thought maybe I wasn't asking appropriate questions.
And even though I had already converted my date columns to Text, I went ahead and switch them back this morning so I could test it out in the Create HTML Table process. I was sure I had already tried that, and was expecting some kind of error in saving or running, but it ran perfectly and the sent email had correct dates in it. I tried so many different lines of code in so many different places I must have just missed that combination. Its hard not understanding the operations or the syntax of the code and then trying to update it with my process specific text without breaking syntax. I feel like this would almost be easier if I just learned JSON and wrote the program rather than trying to build it with the GI. Unfortunately, I just checked over at SoloLearn where I was starting to learn C++, but there is no JSON course 😞
Thanks for the help!

Was this reply helpful? Yes No
Pstork1 68,306 Most Valuable Professional on at

Like (2)

Report

Re: I'm an idiot, cant figure out Excel Date Time Conversion

The date conversion from Excel does work, if the fields in Excel are using the Built-in Date formatting. Its when you specify custom date formatting in Excel that it doesn't know how to interpret it. And yes, there are parts that can be very difficult to get your mind around.

Was this reply helpful? Yes No