SOLVED

Help please! ASAP!

Copper Contributor

Hi, I'm creating a spreadsheet of calls, messages, recordings, etc., for a court case. Unfortunately, because I use Samsung and the contacts I need to document are iPhone users, none of the timestamps match up correctly. When I compare with my phone to my spreadsheet, it matches. The iPhone users are 7 hours ahead in time. How can I subtract these 7 hours to match my local timezone? This is the format I'm using. Notice I send at 15:19/15:20 but it shows 7 hours later as the receiving response is at 22:20. Thanks for the help.

Steve Guzman

Received8/4/2023 22:20
Received8/4/2023 22:20
Received8/4/2023 22:15
Received8/4/2023 22:02
Sent8/4/2023 15:20
Sent8/4/2023 15:19
Sent8/4/2023 15:19

 

Keep in mind i already separated the wrong time zone cells to another tab so i can correct the times, and then i will move it back and combine them.

4 Replies
Keep in mind i already separated the wrong time zone cells to another tab so i can correct the times, and then i will move it back and combine them.
best response confirmed by Hans Vogelaar (MVP)
Solution

@sguzman0023 

If I understand the goal correctly, subtract 7 hours from received items:

 

=IF(A2:A8="Received",B2:B8-(7/24),B2:B8)

Patrick2788_0-1699997194129.png

 

Amazing! ty very much for the help
You're welcome!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@sguzman0023 

If I understand the goal correctly, subtract 7 hours from received items:

 

=IF(A2:A8="Received",B2:B8-(7/24),B2:B8)

Patrick2788_0-1699997194129.png

 

View solution in original post