cancel
Showing results for 
Search instead for 
Did you mean: 

Add functions to convert to/from Excel dates

I'm requesting that Flow add convience functions to easily convert to/from Excel date format.

 

Today I have Microsoft Forms creating records, which are dumped into an Excel for Business spreadsheet. To try and find/compare the date from the Flow output to the Excel Completion Time (date format) column, I have to do some very convoluted steps of:

  1. addDays('1899-12-30T00:00:00Z',int(substring(string(body('Get_Date_UID')?['Completion time']),0,indexOf(string(body('Get_Date_UID')?['Completion time']),'.'))))
  2. div(mul(mul(sub(float(body('Get_Date_UID')?['Completion time']), float(substring(string(body('Get_Date_UID')?['Completion time']), 0, indexOf(string(body('Get_Date_UID')?['Completion time']), '.')))), 100000), 86400), 100000)
  3. add(outputs('Seconds_Past_Midnight_float'),0.000005)
  4. substring(string(outputs('Seconds_Past_Midnight_Round')),0,indexOf(string(outputs('Seconds_Past_Midnight_Round')),'.')
  5. addSeconds(outputs('Completion_Date'), int(outputs('Seconds_Past_Midnight')), 'M/d/yyyy h:mm:ss tt')

All of this to try and convert the Excel date/time for completion time so I can later do Excel Update Row within Flow.

 

It would be much more efficient if I could add a conversion fuction to front end Completion Time to put it into date format localized (MMddyy or whatever localization format string I choose), even if I had to double function it, as in fromExcelDate to convert it to Flow DateTime format and toExcelDate to go from Flow to Excel's weird serialzed format.

 

It was save a ton of computational space. The above fuction may be able to be simplified to one step, but really there should be 2 convience functions for it.

 

 

 
Status: New