creating a date range trigger

[gazzo]

hi i am currently trying to construct a date based maintainance sheet

i will try to explain what im trying to do

in one cell date is inputted
the next cell automatically adds +30 to generate next test date

what i am trying to do is get the sheet to
there are 4 testers so its a 3 month cycle for each ie
tester 1 jan
tester 2 feb
tester 3 march
tester 4 april
tester 1 may
tester 2 june

you get the picture? i hope
so if


cell A1 todays date

a2...................................b2......................................c2

21 jan.............................21 feb................................tester 2


inputed ...................... =A1+30
and date
formated
cell


i am trying to get the formula to tell me after the inputed date has been changed who the next tester is

i have tried using the IF command but it wont let me specify a date range ie 1 jan to 31 jan (or in the specified date codes) date range only one specific date with >= or <= which doesnt quite work properly

i hope this isnt confusing?

[dlh]

Why not just use the row number?

Say your date is in A1.

Put this formula in B1:

Please Login or Register  to view this content.

[gazzo]

here is the sheet and a screenie with explaining notes

[gazzo]

thx dlh but thats not what im trying to do i posted page and screenie to explain it easier I hope hehe

the testing procedure is date driven not by tester name

[dlh]

I'm still don't understand exactly what you're trying to do.

Maybe this is on the right track: use conditional formating in F16 to make the background color BLUE, using the formula:

Please Login or Register  to view this content.

[gazzo]

dlh firstly thank you for your time

look at row 16
C16 is going to be unprotected to allow users to input date of when test done
D16 as you can see from sheet i sent will change if C16 modified
E16 as you can also see is conditionally formatted to represent days to next test (in D16) and colour as needed
the 'j to d' in row 15 represents months

what i want to do is as the date changes to the next test date in D16
**= any date in the month
ie ** jun is for L16 to change 'red' with 'R'
then as the dates changes in D16 to ** jul for M16 to go green with 'G'
then D16 = **Aug then N16 goes blue with 'B'
then D16= **Sep then O16 goes white 'W'


so that i can see who is responsibile for next months test but if the date hasnt changed who should have done it
does that make sense?

[dlh]

Part of what's confusing is you say the month of June corresponds with the color Red, but your attached worksheet (the Month/Watch grid at A33:B44) has June corresponding to White.

So, maybe this is what you're trying to do...

I changed the month names to be "Jan" instead of "J", etc., so I could use them in the formula rather than creating 12 different formulas for each column in F:Q.

I wasn't sure if you wanted the letters "B", "W", "R" and "G" actually printed in the grid F16:Q28, or if you just wanted the cells colored. The enclosed has both.

Best wishes.

[gazzo]

dlh
you are my hero!

its exactly what i needed it to do

although i know this is cheeky could i get a brief explanation of formula?
never used concatenate before

if not no worries

Gary

[dlh]

CONCATENATE("1 ", "Jan") combines two strings to give one: "1 Jan". It was just a way to make Excel think of your column headers as dates rather than as strings.

Enclosed is a slightly simpler version that doesn't use CONCATENATE, because the column headers are changed to full dates (1/1/2007) rather than strings ("Jan").

[gazzo]

Wow
i have been here but a few days and i think my world of excel has tripled with the things i have read on the forum

a big thank you to people like you who are happy to share your knowledge

And the sheet works perfect!

so much so i have edited the formula to give me different more uses for different tests other than monthly ie quarterly, six monthly yearly
again thanks