Time Zone, how to default to PT Time

  • Thread starter Thread starter Joe Wildman
  • Start date Start date
J

Joe Wildman

Hello, I put togeather this code below, can someone tell me how to get this
to show up as Pacific time if you are using a PC not in that time zone? Also
is there a way to make the cell update as the time changes?

=MONTH(NOW()) & "/" & DAY(NOW()) & "/" & YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())
 
Hi Joe
I don't know your time zone, so added 3 hours, you can see in the formula
the 3 so change it to what you need.
This formula will do the same, just format "Custom and select date & time
=NOW()+3/24
Here is you formula with 3 hours added.
=MONTH(NOW()) & "/" & DAY(NOW()) & "/" & YEAR(NOW()) & " " &
HOUR(NOW()+3/24) & ":"& MINUTE(NOW())
HTH
John
 
Well, people from all around the world will be using this so that will not
work, so what i need is anyone from any time zone will default to PT time,
 
Hi,

First point, when a user opens the spreadsheet in another time zone their
system clock is in effect, so that the calculation will apply to their time
zone. The only issue is if you want it to show the PT time not theirs.

In that case you would create a lookup table in the spreadsheet listing all
the time zones in one column and the number of hours to add or subtract in
the second column and you would have the user pick their time zone from a
data validation drop down and use VLOOKUP to find the hours to add or subtract

VLOOKUP(Pick,TimeZoneTable,2,FALSE)

and this formula would go into

=MONTH(NOW()) & "/" & DAY(NOW()) & "/" & YEAR(NOW()) & " " &
HOUR(NOW()+VLOOKUP(Pick,TimeZoneTable,2,FALSE)/23) & ":"& MINUTE(NOW())
 
Back
Top