Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-26-2011, 06:37 AM
derelyth derelyth is offline Add a period of time to a referenced "Time" cell? Windows XP Add a period of time to a referenced "Time" cell? Office 2003
Novice
Add a period of time to a referenced "Time" cell?
 
Join Date: Oct 2009
Posts: 4
derelyth is on a distinguished road
Default Add a period of time to a referenced "Time" cell?

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:



Code:
=C2+5
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:

Code:
Custom - h:mm AM/PM
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
Reply With Quote
  #2  
Old 02-26-2011, 12:15 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Add a period of time to a referenced "Time" cell? Windows 7 32bit Add a period of time to a referenced "Time" cell? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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:
Code:
=C2+5
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
Code:
=C2+(5/24)
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:
Code:
=C2+(E1/24)
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.
Reply With Quote
  #3  
Old 02-27-2011, 05:41 PM
derelyth derelyth is offline Add a period of time to a referenced "Time" cell? Windows XP Add a period of time to a referenced "Time" cell? Office 2007
Novice
Add a period of time to a referenced "Time" cell?
 
Join Date: Oct 2009
Posts: 4
derelyth is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 02-28-2011, 12:24 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Add a period of time to a referenced "Time" cell? Windows 7 32bit Add a period of time to a referenced "Time" cell? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.

Last edited by Colin Legg; 02-28-2011 at 02:40 AM.
Reply With Quote
  #5  
Old 03-27-2011, 04:31 PM
derelyth derelyth is offline Add a period of time to a referenced "Time" cell? Windows XP Add a period of time to a referenced "Time" cell? Office 2007
Novice
Add a period of time to a referenced "Time" cell?
 
Join Date: Oct 2009
Posts: 4
derelyth is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identify "Propose New Time" meeting responses in VBA, and how to response with them frilian Outlook 0 09-02-2010 07:48 AM
"Microsoft Excel Application" missing in the "Component Services" on win08 sword.fish Excel 0 02-26-2010 02:09 PM
Excel error: "Too many different cell formats" enviroko Excel 0 01-09-2008 07:27 AM
Reminder "snooze time" problem Steven Wild Outlook 0 03-08-2006 03:59 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft