[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

Adjust for Daylight Savings Time

Sheet >> https://spreadsheets.google.com/ccc?key=0ApGg6c9aeywQdGZ5VzcxZHljZkJjQmJ5Y2hJMFgzYUE&hl=en  

I need to allow people to select between daylight savings time ON (+1) or OFF (-1) which should adjust the time up/down by one hour on that setting.

If possible, I'd like also to rename the existing selection mouse-overs to say simply "Select A Time Zone" and nothing else.

Assistance is greatly appreciated.
0
rtod2
Asked:
rtod2
  • 3
  • 3
1 Solution
 
Curt LindstromCommented:
Ok, I'm working on this right now. Will post the solution here in a few minutes

Curt
0
 
rtod2Author Commented:
Sorry Curt.  Off should remain unchanged and On should be (+1).  This would make a -6 default setting for central time for instance move up to -5.
0
 
Curt LindstromCommented:
I believe I got this working now.
Formulas have changed to include a lookup from a new small table on sheet3 with ON (+ 1) and OFF (0) adding 1 or 0 to the times.
Formula as added in column G and copied down to the end of current page
=if(A2="","",text(now()+vlookup(B2,sheet3!A:B,2,false)/24+vlookup(C2,sheet3!C:D,2,false)/24+5/24,"HH:mm:ss"))
The same vlookup has been added to the formulas in column D-F

Cheers,
Curt
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
Curt LindstromCommented:
Ted,

I worked that one out :-) I'm adding 1 when ON and 0 when OFF.

Curt
0
 
rtod2Author Commented:
Good stuff.  Can you fix the mouse-over text so that it it only shows the message to the user.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now