adding/subtracting hours from times and dates
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: adding/subtracting hours from times and dates

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Vancouver BC Canada
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a spreadsheet that lists local times for future events all over the world in the following format: yymmddhhmmss. An example would be an event in Australia March 3 at 14:00 local time: 020303140000. When I look at the time in the formula field above the spreadsheet the time is formatted as "03/03/2002 02:00:00 PM" but it appears as I have noted above in the spreadsheet itself. I need to subtract or add hours to get to UTC (eg. Australia is +11 UTC), and then add/subtract more hours to get to my local time (-8 UTC in this case).

    This is probably simple but I can't get it to work. Any help is welcome!!

    keke

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Excel store date/time values as days (and fractional parts thereof). -8/24 subtracts 8 hours; +11/24 adds 11 hours.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Vancouver BC Canada
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-25 09:33, Mark W. wrote:
    Excel store date/time values as days (and fractional parts thereof). -8/24 subtracts 8 hours; +11/24 adds 11 hours.
    So how would that work? Where would I add and subtract? Would it be =(B1-(8/24)) if B1 is the date+time 020303140000 ?

    keke

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-25 09:51, keke wrote:
    On 2002-02-25 09:33, Mark W. wrote:
    Excel store date/time values as days (and fractional parts thereof). -8/24 subtracts 8 hours; +11/24 adds 11 hours.
    So how would that work? Where would I add and subtract? Would it be =(B1-(8/24)) if B1 is the date+time 020303140000 ?

    keke
    Yes, and the parentheses aren't necessary (=B1-8/24 will suffice) because Excel will evaluate the division operator before the minus operator.

    [ This Message was edited by: Mark W. on 2002-02-25 09:55 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com