Hi all,

Sorry if the title doesn't make much sense, I'm not sure what I want to do would be called. If a Mod knows of a better title, please feel free to change it

Using Excel 2007, I've downloaded a Baseball schedule in CSV format to import into my Outlook 2007 calendar. However, being in the UK my timezone is GMT and therefore +5 over the ET that is in the schedule.

There are two columns, C is marked START_TIME_ET, B is START_TIME. Column B is designed apparently for your own timezone - and as such I tried to reference cell C2 as below:

In hope that it would change the Time of 1:05 PM in C2 to 6:05 PM in B2 - I would of then imported that column as my start time for my Outlook calendar.

However the formula did nothing. So I checked the cell format and it was:

As such, the data in the cell for to produce 1:05 PM for example is 13:05:00 - would this have any bearing on the reference working or not? Is there a way to achieve what I need?



If anyone could help, I'd appreciate it - I'm not overly clued up on Excel

Many thanks,

Karl

Hi Karl,

In Excel, a day (or 24 hours) is 1.
Therefore an hour is 1/24, a minute is 1/24/60, etc..

In your formula:
You are adding 5 days to C2. Since the format is h:mm AM/PM, it looks like the formula does nothing because the day portion is not displayed.

So if you wanted to add five hours you would use
What would be even better would be to have a cell which you put the required hour adjustment into. Say this is cell E1. Your formula then becomes:
Any other formulas which need the hour adjustment could also reference E1. This means that if you ever wanted to change the hour adjustment you could simply change the hours in E1.

Thanks Colin, that makes good sense and worked well

Two further things have cropped up, one from your reply, another from the time adjustment.

1) You mention for example using a single cell to reference to - considering the DST/BST etc time changes that makes sense. It worked for the first cell in the column, however if I used the little black cross symbol that pops up when you hover bottom-right of a cell to then replicate the formula down the next 195 cells in the column, (for example) (E1/24) then became (E2/24), (E3/24) and so on. Is there a cell format to change to stop that and to make each cell reference only E1 and not each consecutive E cell?

2) There is a column in the sheet with the Game dates in - labelled "START_DATE". Some of the games start at for example 7:05PM ET. Add 5 hours for my timezone and that becomes 12:05AM UTC. That is obviously into the next day, and so I was wondering is there a way to adjust the date (in a new column, as similar to the time adjustment) to change the date if the Start Time goes over 12AM?

Thanks again,

Karl

Hi Karl,

You can put a $ sign in front of column and/or row references to lock them when you copy/paste/fill.
E$1 would lock the 1 (for when you fill down a column)
$E1 would lock the E (for when you fill across a row)
$E$1 locks both.
E1 locks neither.

Accounting for the date roll should just be a case of adding the date, time and time adjustment together. If you have any trouble then feel free to attach the workbook to the thread.

Hi Colin,

Very sorry for the delayed reply, University has taken over the last few months! As such, in the end I gave up with the CSV file when I realised the dates were going into my Calendar wrong due to the US date display.

However I must stress that your replies have not been in vein! The information, especially for locking cell references has helped me further develop some other spreadsheets including ones used for my business so I am extremely grateful for that.

Sorry once again and thank you for your time,

Karl

P.S. - Rep left