Change the Time in Date/Time Cells

user_14897645 0
Viewed 43

I am trying to change the time in each Date/Time cell so that the time is 5:00:00. So that 05/10/2021 07:00:00 becomes 05/10/2021 05:00:00.

I have tried turning the cell into plain text, splitting it by the decimal and rejoining with the appropriate time, but it breaks my other formulas even though the format looks identical. I have also tried splitting the date and time, then using '''=CONCATENATE(text(A2,"M/D/YYYY")& " " &text($B$2,"H:MM:SS"))''' - the formulas still break.

google-sheets if-statement timestamp date datetime
1 Answers

if this is a valid DateTime cell just go into spreadsheet settings and change the timezone of your sheet to -2

update:

=INDEX(IF(A1:A="",,TEXT(INT(A1:A), "dd/mm/yyyy")&" 05:00:00"))

enter image description here

user_56326290
Related