Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

(0) Share

Report

Posted on by Pipo

Hi to all,

Picking dates from Excel y get this output YYYY-MM-DDThh:mm:ssZ as you may see in the image.

I need to send an email with that date.

Any idea how to convert the format to DD-MM-YYYY without the time?

Don't know how to use Compose to make it.

I've obtained the desired format inside the Excel creating an extra column an using TEXT function to get the desired format buy I would like to make inside the flow without modifying the Excel file.

Thanks a lot

I have the same question (0)

All responses (9)

Answers (1)

abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Hi @Pipo

Use the formatDateTime() expression.

formatDateTime(yourdynamicdatefieldvalue, 'dd/MM/yyyy),

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Hi @Pipo

I have realised that the format date you have is not dd-mm-yyyy hh:mm:ss format. So you will struggle to convert using the formatDateTime() expression. Please follow the below steps

Above the split compose expression is as follows:

split(first(split(outputs('Compose_2'),'T')),'-')

Next compose to get the date format in dd-mm-yyyy the expression I used above is as follows:

outputs('Compose')[2]-outputs('Compose')[1]-outputs('Compose')[0]

For the above use one outputs each time and click update then manually type the hyphen '-' and do the next output index and so on.

Here is my test result

Was this reply helpful? Yes No
Verified answer

Pstork1 68,271 Most Valuable Professional on at

Like (1)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Actually, the date is in ISO 8601 UTC time zone format. A formatDateTime() will work just fine without the extra steps. But be aware Excel using this format was due to a regression bug. MS is in the process of rolling it back so it will start reporting the Serial date again which will need additional conversion.

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Hi @Pstork1

I did tried initially formatDateTime() and I got the below error. Am I missing anything here?

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

Like (0)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

If you look at the original post you will see that the date was reported with a 'Z' at the end. That is the way it comes from Excel (At least until MS change the connector back). The UTC timecode will convert directly using formatdatetime. How are you getting the time you have in Compose 2? That appears to come in as a local time zone date time.

Was this reply helpful? Yes No
abm abm 32,585 Most Valuable Professional on at

Like (0)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Hi @Pstork1

Thanks. It was my bad I forgot to add the Z in my compose. I was trying have a quick test against the value which is provided from the screenshot earlier.

Thanks

Thanks

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

Like (1)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Not a problem. I didn't notice it either on your post. That's why I wanted to clarify that extra steps weren't necessary. But it won't matter since that update is being rolled back and we'll have to go back to calculating date and time from a serial date.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

I have a similar problem. I work on a mac and have .csv files that need to be changed to the ISO 8601 format. I cannot find a way to do it and all the answers I found online don't work for me..I am sure I am missing a step and am not an excel wizard at all...if anyone can help I would appreciate it.

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

Like (1)

Report

Re: Date Format YYYY-MM-DDThh:mm:ssZ to DD-MM-YYYY

Please post as a new post and add some screenshots of what you have tried. Send me a link and I'll try to help. But it will get more help as a new post then tagged to the end of an existing one. Others will also be able to find the eventual answer easier.

Was this reply helpful? Yes No