auto changing cell colour

[YYX99]

I hope some one can help

in a spreadsheet that I have to setup, I have comeup against 2 problems I cant work out.

1/ in 1 cell the time a load is due out is manually entered, a formula then subtracts 45 mins and enters this time in the next cell for scheduled load by time. not a problem until the load by time crosses over the 00:00, this then stops calculating due to different days, is there a fomula to subtract time into the prevoius 24 hrs.

2/ I need to make a cell change colour if the load by time is 4 hours prior to the sheduled time, or if it is loaded before 00:00, to indicate pre loading.

any help in these problems would be great.

[dominicb]

Good morning YYX99

1/ in 1 cell the time a load is due out is manually entered, a formula then subtracts 45 mins and enters this time in the next cell for scheduled load by time. not a problem until the load by time crosses over the 00:00, this then stops calculating due to different days, is there a fomula to subtract time into the prevoius 24 hrs.

You will ned to show the date and time for Excel to figure out what you want to do, eg in a cell :
16/05/07 00:01
say this is in cell C1, to put the result of four hours prior to this in cell A1 you would use :
=A1-.166666
ie 4(hours) / 24 (hours in a day)
Make sure your cells are formatted accordingly - although your cell needs to contain the date, your format doesn't have to show it.

2/ I need to make a cell change colour if the load by time is 4 hours prior to the sheduled time, or if it is loaded before 00:00, to indicate pre loading.

Sounds like you need conditional formatting, but I can't get my head round what you want to do. Can you expand?

HTH

DominicB

[YYX99]

would it help for me to send a sample copy highlighting problems or by showing it on this thread,

thanks for the help, very stumped at this time

[YYX99]

dominicb,

Sorry to sound thick, your respnse says about the date, can this be done by having the date in a different cell on the spreadsheet, as this is already there at top, could it be referencd in the formulas.

Condiotnal formating ??

I need to have a cell change colour depending on weather the load has been completed 4 hours in advance (RED) and (BLACK) if it is completed 1 hour late. cell to do nothing if within these. time would generate once problem one is sorted.

[dominicb]

Hi YYX99

Have a look at the sample worksheet. It shows an example of 12:30 in the morning, and subtracts 4 hours from it to show 8:30 the previous evening. I've then appliked a format to it so although the date is still there, the cell only shows the times.

Condiotnal formating ??

I need to have a cell change colour depending on weather the load has been completed 4 hours in advance (RED) and (BLACK) if it is completed 1 hour late. cell to do nothing if within these. time would generate once problem one is sorted

Conditional formatting is under the Format menu and sounds like just the thing to help you out - but I still don't understand what you need. How do you know if the load is completed 4 hours in advance? How do you know if it is completed 1 hour early?

HTH

DominicB

[YYX99]

sorry,

when a load is complete a time is manually entered into another cell.

need to use this info against sheduled load by time cell to produce the condition