How to change Time Zone of a long column of time entries

[ve3oat]

Is there an easy way to correct a long column of time entries by a specific number of hours, that is, to change the Time Zone of the entries?
Recently my data logging device was mistakenly set for UTC (old GMT) instead of the local time zone, so 7 days worth of data (some 1900 data entries) were stamped with times and dates advanced by 5 hours. I tried many things which didn’t work, and ended up creating an extra column with the corrected times using

=TIME(HOUR(B72)-5,MINUTE(B72),SECOND(B72))

but of course that caused some “negative” times (errors) which I had to correct manually, and of course the corresponding dates as well, manually. It took some time to do all of this, no pun intended.

In case this problem happens again, it might help to know if there is an easier (faster) way to simply correct a column of 24-hour times by an exact number of hours, plus or minus, as if changing the Time Zone of them. It would be preferable if the new times could appear in the same column as the old times, but I realize that might not be possible (REFERENCE errors) and that the layout of the spreadsheet would have to be modified to accommodate the corrected times (as I did in my own “solution”).

Using LibreOffice v6.4.7.2 under Ubuntu 20.04.

[ajlittoz]

The error in your formula was to split the number representing date and time into its components and process each component separately.

Date and time is a floating-point number with the integral part being the number of days since the epoch and the fractional part the fraction of a day.

Therefore, to fix UTC time to UTC-5, just subtract from all the numbers -5/24. This will automatically take care of the day since the borrow will decrement the integral part, giving you the previous day.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

[ve3oat]

@ajlittoz You have done it again! Thank you very much! I used

=B72-5/24

and it gave the correct answer throughout, fixing the negative (error) time problem. (The date column in my spreadsheet I can fix by copy and paste for each group of incorrect dates.)

Date and time is a floating-point number with the integral part being the number of days since the epoch and the fractional part the fraction of a day.

Never realized that Calc treated date and time that way. Thank you! I certainly wish that the LibreOffice Guides had examples of usage in them as it might have helped me to find my own error.