calculate time zones

Topic

New Reply

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

Reply | Quote

Replies

Karat,

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

37242-Screenshot-163

Reply | Quote

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

Reply | Quote

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

Reply | Quote