Can i download responses in different time format for submission date? I want to see submission date in IST.
To format the dateTime string exported by Central (in cell A2 in the screenshot below) to something Excel can understand, you can pull out just the date and time (ignoring the "T", milliseconds, and "Z"). Then add them, as the math operation (+) causes Excel to automatically coerce the text values to numbers (which is how Excel stores dateTimes). The formula in cell B2 does this using LEFT and MID:
=LEFT(A2,10)+MID(A2,12,8)
In column C I have the number of hours offset for the time zone conversion, and then I change the time using the formula in cell D2:
=B2+(C2/24)
I could also just include the offset number in the formula (here the +5.5 hour difference of IST):
=B2+(5.5/24)
You can change the cell type to change the display from the decimal number to the formatted date string (as shown in B3 versus B2).
dateTimeStringConversion.xlsx (12.1 KB)
WARNING: If you convert to a time zone that observes daylight savings or other annual adjustments and your data collection crosses the date of the adjustment, it will complicate your calculations!
Thanks for the response, but we dont want to use this formula every time we download excel, can we customize it?