Answered

Need help to fix date issue

(0) Share

Report

Posted on by Priyanka03

I have excel data and it has the date column of created.

Its in power automate.I'm using excel as connector. I used the formula for adddays('1899/12/30',int(item()?['Created']),'yyyy/MM/dd') is not working it says the template language int is invoked.value cannot be converted as target type.

I used the other formula for

div

(

sub

(

ticks(formatDateTime(item()?['Created'], 'yyyy-MM-ddT00:00:00')),

599264352000000000),

864000000000

)

It says its not in iso format.

Kindly let me know how to go about this.

Thanks.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

Sort by

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Priyanka03 ,

When querying data from excel, set the Date time format to ISO 8601.

What is your logic behind adding days?

Was this reply helpful? Yes No
Priyanka03 2 on at

Like
a
(0)

Report
Copy link

Link copied!

@SudeepGhatakNZ ,
I need to filter the older 60 days of record (created Column) from current date.
Later i need to check the date difference between created to completed .
If i put that option in list rows then i'm trying to format the date as formatdatetime(item()?['Created'],'MM/dd/yyyy') it throws an error that is not iso format.

Was this reply helpful? Yes No
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

If you add item()?['Created'] in a compose message what does it print?

Was this reply helpful? Yes No
Priyanka03 2 on at

Like
a
(0)

Report
Copy link

Link copied!

I put the same in iso format in list rows but all of my date column is changed into iso format except one column is like 45096 this format. so if i'm trying to formatdatetime(item()?['Accepted'],'MM/dd/yyy')
it throws an error string doesn't match the iso format.

Could you please tell me how to overcome this?

Was this reply helpful? Yes No
Verified answer

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Please format the column with the Date option. I think it is currently set to General

Why is Excel Changing Dates to Random Numbers? | Date Changes to 5 Digit Number - YouTube

Was this reply helpful? Yes No