Show Current Time in Excel by Timezone

Question

I have searched all over and any answer or code I find is rather confusing.

I am looking to convert the =NOW function in excel to a different timezone(s)

I am currently using

=TEXT(NOW(), "hh:mm:ss AM/PM")

which displays my timezone EST

I would like to convert this to another time zone

for example PST.

is there a way to do

=TEXT IF C1 equals PST subtract 3 from A1 <--- the current time

what im trying to do is if one column has the letters PST in it I want to convert the =NOW by subtracting 3 hours from my EST Time Zone.

sorry if my question is a little confusing.

I know im not the only one searching this so an answer would help me and future users, thanks so much in advance.

UPDATE:

so I was able to use

 =TEXT(NOW()-0.125, "hh:mm:ss AM/PM") 

to subtract 3 hours, but not make an if statement for 2 or more timezones

Solution 1

What about when day light savings occurs. You want to use the HOURS() function in Excel and subtract the necessary hours for each times zone. You'll also need to handle crossing midnight in your calculation. If you just want to play around with what you have started see the below link for something more accurate to your stated post.

Check this link add and subtract time in excel

Try this:

=IF(B3="PST", =TEXT(NOW()-0.125, "hh:mm:ss AM/PM") ,=TEXT(NOW()))

Solution 2

Try this formula in B3 cell for individual

=TEXT(NOW()-TIME(hour,minute,second),"hh:mm:ss AM/PM")

and by using IF statement for 2 or more timezones in B6 cell

 =IF(B1="GST",TEXT($A$1-TIME(1,30,0),"hh:mm:ss AM/PM"),
  IF(B1="BST",TEXT($A$1-TIME(4,30,0),"hh:mm:ss AM/PM"),
  IF(B1="EST",TEXT($A$1-TIME(9,30,0),"hh:mm:ss AM/PM"),
  IF(B1="PST",TEXT($A$1-TIME(12,30,0),"hh:mm:ss AM/PM"),
  IF(B1="AST",TEXT($A$1-TIME(6,30,0),"hh:mm:ss AM/PM"),
  IF(B1="ACST",TEXT($A$1-TIME(2,30,0),"hh:mm:ss AM/PM")))))))

---

Comments

[user3893380]

thanks for the reply! I was able to manually do it by placing =TEXT(NOW()-0.125, "hh:mm:ss AM/PM") inside the time column. is there a way to add an IF statement so I dont have to manually do it?

[htm11h]

Yes...=IF(cell='PST',True condition, False condition)

[htm11h]

More accurately something like this....=IF(B3="PST", =TEXT(NOW()-0.125, "hh:mm:ss AM/PM") ,=TEXT(NOW()))

[Matt Johnson-Pint]

This isn't an answer, it should be a comment instead.