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

how to format date and time (hour, minute and second) when converting excel to csv?

I have a table in onedrive and i need to convert it to csv but the date and time columns (HH: mm: ss) when converted to csv are with integer values, how can i make this conversion to the date format stay as date and time how time?

My table

my table.jpg

my csv

Supersan123_1-1615160697257.png

I tried to put the date value in the variable and turn it into a string but it gave an error

1.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
v-yiwenxie-msft
Microsoft
Microsoft

Hi @Supersan123 

 

Yes, I haven't got any error for my test.

int() can only be used for an integer and float() can only be used with a float.

Could you show me your expression and your data with private info convered up?

But it seems like your data looks like:

3.9-1.PNG

The date column shoud be used with int() if the date column doesn't contains time and time column should be used with float().

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
PrasadAthalye
Community Champion
Community Champion

You are Initializing Variable within a loop. That is not allowed. Initialize it outside and then use a SET variable.

 

For formatting can you convert it to a string using the string function? In the SET Variable, use the expression and write string function and select DATA.

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
v-yiwenxie-msft
Microsoft
Microsoft

Hi @Supersan123 

 

It seems like you want to convert your date column and time column into the format of ‘yyyy-MM-dd’ and ‘HH:mm:ss’ respectively.

I’ve made a test and have run it successfully.

 

The date column and time column in excel file which will be needed to converted to csv file looks like:

3.8-1.PNG

 

If I do not use any function, then the csv file created will look like:

3.8-2.PNG

 

If I use the function, then the csv file created will look like (which will suit your need):

3.8-3.PNG

 

You can try the following steps and expressions:

3.8-4.PNG

The expression used:

addseconds('1899-12-30', int(formatNumber(mul(int(item()?['Date']),86400),'0','en-us')),'yyyy-MM-dd')

addseconds('1899-12-30', int(formatNumber(mul(float(item()?['Time']),86400),'0','en-us')),'HH:mm:ss')

 

The 'Date' and 'Time' in the two expressions are the two column names in my excel. You can input yours in the two expressions. 

 

For more info about the function:

addseconds()int()formatNumber()mul()float()item().     

 

If you still have the problem, please feel free to let me know.

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the help, I did it as described but I got this error, and searching I didn't understand why it complains that the int () has no value valid since we have value and is even formatted, in your did not appear this error?

Supersan123_0-1615206633257.png

 

v-yiwenxie-msft
Microsoft
Microsoft

Hi @Supersan123 

 

Yes, I haven't got any error for my test.

int() can only be used for an integer and float() can only be used with a float.

Could you show me your expression and your data with private info convered up?

But it seems like your data looks like:

3.9-1.PNG

The date column shoud be used with int() if the date column doesn't contains time and time column should be used with float().

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,653)