I have just spent the best part of the day writing a complex expression to reformat a string date into a UTC date that I can load into a SharePoint list.
It’s messy. And I’m sure it can be improved.
The starting string is, which I load into a Compose action called “Event Date”:
13/06/2018 – 8:30 am – 5:30 pm
And the output format must be a UTC formatted date:
2018–06–13T08:30:00Z
To kick things off, my working version is:
concat(concat(concat(concat(concat(concat(concat(substring(outputs(‘Event_Date’),6,4),’-’),substring(outputs(‘Event_Date’),3,2)),’-’),substring(outputs(‘Event_Date’),0,2)),’T’),concat(concat(concat(if(equals(trim(split(split(outputs(‘Event_Date’),’ — ‘)[1],’ ‘)[1]),’am’), if(equals(length(split(split(split(outputs(‘Event_Date’),’ — ‘)[1],’ ‘)[0],’:’)[0]),2), split(split(split(outputs(‘Event_Date’),’ — ‘)[1],’ ‘)[0],’:’)[0], concat(‘0’,split(split(split(outputs(‘Event_Date’),’ — ‘)[1],’ ‘)[0],’:’)[0])), string(add(int(split(split(split(outputs(‘Event_Date’),’ — ‘)[1],’ ‘)[0],’:’)[0]),12))), ‘:’), split(split(split(outputs(‘Event_Date’),’ — ‘)[1],’ ‘)[0], ‘:’)[1]), ‘:00’)),’Z’)
I told you it wasn’t pretty.
We are not interested in the end time here, just the UTC representation of the Start Date and Time.
There no prizes other that Community Kudos, self-gratification and smugness. Points will be awarded for shortness and elegance.