Results 1 to 3 of 3
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Adjusting dates for DST (2003)

    I have one date field: A1=09/09/2007 11:45:45 Sun. The time is UTC. Is there a function (=IF?) that I can use, to subtract 6 hours from the time, if the date portion of the field falls between two dates, or subtract 7 hours if it does not? I could create two separate fields from the one, i.e., A1=09/09/2007, B1=11:45:45, if it would be easier. Thanks.
    JimmyW
    Helena, MT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adjusting dates for DST (2003)

    Let's say the start date of the special period is in B1 and the end date in B2.
    The date/time is in B4
    The following formula will subtract either 6 or 7 hours from the date/time in B4, depending on whether is is between B1 and B2 or not:

    =B4-TIME(6+IF(AND(B4>=B1,B4<=B2),0,1),0,0)

    See attached sample workbook.
    Attached Files Attached Files

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Adjusting dates for DST (2003)

    Thanks very much, Hans!
    JimmyW
    Helena, MT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •