Updating time in realtime based on city in preceding cell

I am kind of novice in Excel and designing a spreadsheet for my employer. This is what i am trying to do. First, i am trying to figure out, how to automatically update time, based on specific city's time zone? Lets say, if the city is dallas, time zone is Central GMT-6. In the next cell, the time shows up as Dallas's local time, instead of local computer time. And this time updates itself after every 2 min.

2ndly, if that city's time is later than 8:59 PM local time, that specific city's cell grays out.

Any help will be highly appreciated. I am lost

Attach: Snapshot of sheet

Hi,

You can use the function =Now() and format the cell as you wish ...
Then you can add or substract whatever hours difference, and use conditional formatting for colors ...

HTH

The problem is that =NOW() will not show the time in that state. I am in Texas and lets say that New York local time needs to be updated after every 2 min or so. How will a sec synchronize the time and make sure its the right time zone?

Hi,

Attached is a sample file ...

HTH

If state=TX, time in the next colum should change automtically to =NOW()-TIME(0,0,0), since local machine is in central time zone.

But how to do this trick,if local machine is in Central, but the state is in Pacific and when the state abbreviation is changed, the time zone automatically recognizes the change and updates the cell accordingly.

Based on JeanRage's attachment. The macro was "googled", since i have no usefull knowledge of VBA. It should update NOW() every 20 seconds.

WHER
This is awesomeeeestest... but there is a sligh problem, when i try pasting state in cells, lets say i wanna paste NY, instead of Dallas, it gives an error message. Anyway possible that i can copy paste states and the time recognizes the change and updates itself?

I really appreciate the work done. You are an extremely helpful person

The state names (like NY) that you enter in C2, C3 etc.. by copy/paste or typing or validation list, should be spelled exactly the same as the state names in the lookup table in J2, J3 etc. So: entering "NY" in C2 while the lookup table J2, J3 etc.. contains "NewYork" does not work. Therefore in my example i used J2:J5 as validation list for C2:C5.

Attached is the shot of error

Kool beans, it works, you guys are awesome. one last thing though. If i want the cell to greay out after 8:59:00 PM, what do i need to do, i tried =IF(D2>8:59:00 PM,"No","Yes"), this doesn't work.



Also the time is not updating automatically, i have attached the sheet for anyone to take a look at.

For the greying out try this:
select C2, C3, etc.. down as far as needed, go to format >> conditional format >> formula is >> =TIME(HOUR(D2),MINUTE(D2),SECOND(D2))>0,874305556
When i opened the attached workbook, the time updated ok.

Well, time is updating fine, but cells are not graying out.

I tried entering the formula with '=' sign and it does not accept it, without '=' sign, it accepts it, but the cells dont gray out after 8:58:00 PM . Please help me out

I tried entering the formula, it didn't work, so i removed it ;(

Hi,

Check if you do not have conflicts within your formatting conditions ...

As far as turning cells to grey after 8:58 PM, test formula

=(D2-INT(D2))>0.874305556

HTH

Hi,
in the attachment below your workbook with conditional formatting applied,
in column C with JeanRages formula, in column D with the formula i provided, both work for me.

One last question for you guys. The cells are graying out just fine after 8:58 PM. Anyway they can white out after 7:58 am. So they are supposed to remain grayed out from 8:58PM till 7:58 AM. Is it possible?


I dont know what i would have done without you guys' help. You all are awesome

Hi,

You only need to add a second condition

=(D2-INT(D2))<0.325

HTH