formatting a spreadsheet help

[hj]

I have a few different dilemas going on here.
First: Can a conditional format be set up in Vista or Windows 7 both 64 bit,
and saved in 97-2003 excel saving the conditional formats?
Second: I have a spreadsheet column A is my received date, Column B is Stock
#, Column C is Serial #, Column G is my finished date.
What I need to be able to do: When I enter information into Column C (Serial
#), I need some type of notification if that same number has been entered
within a six month time period from my Column G (Finished date).

 

[Fred Smith]

1. Yes. However, there are some formats which only Excel 2007 can handle.
But when you save the file, Excel will tell you if you've done anything that
can't be supported in 2003. If you get that message, then fix those items.

2. You'll likely need to give an example to get a decent answer to this
question. How do you want a serial number (column C) compared to a date
(column G)?

Regards,
Fred

 

[hj]

Thank you Fred for your quick response. I have to track the serial # for
warranty purposes within a six month period. A customer gives me something to
fix, I give him a six month warranty, but if the same part (tracked by the
serial #, column C) comes back to me within six months from the finished
date(column G) and I enter the same Serial # into my spreadsheet is there a
way to let me know such Serial #(column C) was entered within the last six
months?

 

[Fred Smith]

I would use a formula like:
=sumproduct((c$1:c100=c101)*(g$1:g100>today()-180))
and put this in, say, column H. Adjust the ranges to suit.

This will give you the number of times the given serial number appears in
the previous 180 days. In other formulas (or conditional formats) you can
check to see if the number is greater than 0. If you really need 6 months,
rather than 180 days, use: date(year(today()),month(today()-6),day(today())

Regards,
Fred

 

[Billns]

I would use a formula like:
=sumproduct((c$1:c100=c101)*(g$1:g100>today()-180))
and put this in, say, column H. Adjust the ranges to suit.

This will give you the number of times the given serial number appears
in the previous 180 days. In other formulas (or conditional formats) you
can check to see if the number is greater than 0. If you really need 6
months, rather than 180 days, use:
date(year(today()),month(today()-6),day(today())

Regards,
Fred

Give the customers a break! Make it 183 days, which is half a leap year.

Bill