| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Zone, how to default to PT Time
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()) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Zone, how to default to PT Time
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 "Joe Wildman" wrote in message ... 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()) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Zone, how to default to PT Time
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, "John" wrote: 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 "Joe Wildman" wrote in message ... 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()) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Zone, how to default to PT Time
See Chip Pearson's site for working with Time Zones
http://www.cpearson.com/excel/TimeZo...lightTime.aspx At least one of the Functions will serve your needs to replace the +3/24 Gord Dibben MS Excel MVP On Sat, 7 Feb 2009 11:17:01 -0800, Joe Wildman wrote: 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, "John" wrote: 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 "Joe Wildman" wrote in message ... 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()) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Zone, how to 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()) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Joe Wildman" wrote: 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, "John" wrote: 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 "Joe Wildman" wrote in message ... 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()) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Zone, how to default to PT Time
OK I will give it a shot, but i got one more fish to fry in the mix thanks!
"Shane Devenshire" wrote: 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()) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Joe Wildman" wrote: 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, "John" wrote: 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 "Joe Wildman" wrote in message ... 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()) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tracking Time Zone | Excel Discussion (Misc queries) | |||
| convert time from one zone to other zone in excel | Excel Worksheet Functions | |||
| Time Zone.. | Excel Discussion (Misc queries) | |||
| How can i find time in various time zone? | New Users to Excel | |||
| Time zone Time difference | Excel Worksheet Functions | |||
![Joe Wildman[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
