Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    calculate time zones

    Hi there
    I have a some spreadsheet with dates and times of the Commonwealth games.
    For instance the first netball match is on at Friday July 25 9:30am. That is their time zone. Our time zone is 11 hours ahead.
    So I did d5+time(11,0,0) and I got the dreaded #value! sign

    It should say Friday July 25 10:30pm

    I obviously did something wrong. Pls help

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Karat,

    Seems to work fine for me using Excel 2010 SP-2.
    time.JPG
    Are you sure your value in D5 is a valid Date time value? I entered mine this way...
    7/25/2014 09:00

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,656
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Karat, did you include the text "am" when you entered the date?

    I've experimented with Excel 2013 and get the #value! error if I include "am" but not if I exclude it. Presumably Excel works in 24 hour notation by default and throws a wobbly with unexpected text within the cell.

    To get Excel to display the desired date and time in the format you wish requires a custom format, this seems to match what you want

    dddd mmmm dd, h:mm AM/PM

    Screenshot (163).png
    Last edited by Browni; 2014-06-22 at 19:31. Reason: Add screenshot

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    July 25 9:30am is probably text and not a valid date. put the space in and it should be converted:
    July 25 9:30 am

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi people
    Thanks for all the suggestions.. I have re-entered it all and that seem to work.
    Can't miss any of the matches

    Thanks again

Posting Permissions

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