Unanswered

Date shows as number in Excel row, occassionally

(0) Share

Report

Posted on by The21k

I have a flow that reads a document, extract some values and put it in rows in a Excel table. One of those values is a date, each Excel action has date time format ISO 8601 so normally i get dates with that format. Sometimes, the date is presented as a number like 45530. Im pretty sure thats somehow converted to a date, which the date shows in power automate, in the output of the excel action which puts the date, but on the Excel file it shows as a number. I'd like for it to always show as a date on Excel. Of the images below, there are 3 numbers which on the history of the run in power automate, shows as a date. How do i make it always a date? or at least guarantee that in the excel sheet is gonna show up as a date?

Date shown as number and dateSame exact cell shown as a date in power automate, while as a number in excel

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

It seems odd that you would have two different date formats in your spreadsheet. That I cannot explain. But, I suspect that the one that is just a number is the serial date. If you add that number of days to 1899-12-30 you would get 2024-02-06. Does February 6, 2024 seem like it would be the correct date for the item where it is listed as 45309? If so, you can use an expression to add that number of days to 1899-12-30. This is an expression from one of my flows with a similar problem, but all rows have the date formatted as the serial date:

addDays('1899-12-30', int(items('Apply_to_each')?['Date']), 'yyyy-MM-dd')

Was this reply helpful? Yes No
The21k 33 on at

Like (1)

Report
Thanks for your answer David! I've done a very similar thing when operating with the date i've shown to calculate another date, my function goes as:

if(isInt(body('Update_a_row')?['Fecha sobre']), addDays('1899-12-30', int(body('Update_a_row')?['Fecha sobre']), 'g'), body('Update_a_row')?['Fecha sobre'])

So it checks if it's an int before the operation because weirdly, sometimes is an int and other times is a date. I'd like a different solution than this because i already have a function to insert my previous date, the one i've shown, but meanwhile i'll try to merge both functions, if it works i'll mark this as solved, thanks again! If anyone has a simpler solution i'd appreciate it very much.

EDIT: I've merged both functions and it seems to work, here's the result function in case anyone needs to see it:
if(equals('date', null), '0', if(isInt('date'), addDays('1899-12-30', int('date'), 'g'), formatDateTime('date', 'dd-MM-yyyy')))
For simplicity's sake i've replaced my data extracted date as 'date', this function first checks if it's a null (didn't found date on document), then if there is a date, it checks if its an int, if it is it adds the number to the date as David pointed out, if not its just the date.

Was this reply helpful? Yes No