Change the Time in Date/Time Cells-CodePudding

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.

CodePudding user response：

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"))

CodePudding user response：

Let's break it down to small achievable pieces:

Extract Date from a Date/Time cell
Add a fixed time of 05:00:00 / offset time by -2 hours
Combine the two

Given A1 Date/Time cell, lets set B1 with the following:

Option 1: Fixed time

=DATE(YEAR(A1), MONTH(A1), DAY(A1)) TIME(5, 0, 0)

Option 2: Offset timezone

Reduction of 2 hours is achievable in this way:

=DATE(YEAR(A1), MONTH(A1), DAY(A1)) TIME(HOUR(A1)-2, MINUTE(A1), SECOND(A1))