cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amodtiwari
Helper II
Helper II

Date value is not coming in date format while creating csv from excel

Hi All,

 

i am working on a flow to read excel file from sharepoint library and create csv file to one drive. 

 

whole logic is working and csv is generating successfully. 

Issue- Facing issue with date field which is showing value as 44379 in csv.

 

Excel field has correct date format e.g.- 7/2/2021. 

 

while trying to format excel date field value under create csv custom column input, its throwing error-

'The execution of template action 'Create_CSV_table' failed. The column values could not be evaluated: 'In function 'formatDateTime', the value provided for date time string '44379' was not valid. The datetime string must match ISO 8601 format.'.

 

please help me , not sure how to fix this. 

 

Thanks 

Amod

 

2 REPLIES 2
RobElliott
Super User
Super User

@amodtiwari Excel stores a date as an integer - the number of days since 30 December 1899 - so you need to add an expression, for example in a Compose control, to convert it back. So if you are getting a series of rows from the spreadsheet the expression would look like this (the column in my spreadsheet with the date is called ReviewDate and you can alter the format to whatever your local format is:

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['ReviewDate']),-2)),'dd/MM/yyyy')

 

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

amodtiwari
Helper II
Helper II

Hi Rob,

 

Thanks for quick response. i got it working by placing below under create csv custom columns-

addDays('1899-12-30',int(item()?['fieldname']),'yyyy-MM-dd')

 

 

Thanks 

Amod

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!

Top Solution Authors
Top Kudoed Authors
Users online (1,316)