Hi everyone.
I have an excel spreadsheet that contains contractor details, including start and end dates and number of days worked each month, in a row with months across the top from Jan to Dec (formatted as 'date). Each of the contractors managers will be responsible for entering the number of days worked per month. They are entered in the contractor row but under the appropriate monthly heading.
What I need to do is to only allow entries to be made in the month column if the month falls between the start and end dates - or at least have a message appear if an entry is made in a month where it should not be. There may be cases where the contract continues but the end date is not updated officially for a month or two so I would like any solution to check the start date only. Each monthly column has that month e.g. "May-07" at the top. Contract start and end dates are also formatted as dates
e.g
Name Start End Jan-07 Feb-07 Mar-07 .......etc
Joe 01/01/07 31/07/07 21 22 18
Nick 01/01/07 31/07/07 25 19 25
If the manager was to make an entry for Dec-06 I would need an error message to say that the entry is prior to contract start date - and therefore possibly refuse the entry. If the manager was to make an entry in Aug-07 then I would like a message as before but to allow the entry to continue (contract being extended but not yet notified)
I've tried conditional formatting but have found nothin that suites my scenario. I've also tried a hidden column whereby if the date is erronious then that cell is populated with a message - but ideally I would have like to have this column hidden.
If anyone can help or point me in another direction I would be grateful
Many thanks in advance
BTW, I'm on annual leave until 22nd October - visiting our "cousins" in the US and Canada for three weeks - New England in the fall
Chris
I have an excel spreadsheet that contains contractor details, including start and end dates and number of days worked each month, in a row with months across the top from Jan to Dec (formatted as 'date). Each of the contractors managers will be responsible for entering the number of days worked per month. They are entered in the contractor row but under the appropriate monthly heading.
What I need to do is to only allow entries to be made in the month column if the month falls between the start and end dates - or at least have a message appear if an entry is made in a month where it should not be. There may be cases where the contract continues but the end date is not updated officially for a month or two so I would like any solution to check the start date only. Each monthly column has that month e.g. "May-07" at the top. Contract start and end dates are also formatted as dates
e.g
Name Start End Jan-07 Feb-07 Mar-07 .......etc
Joe 01/01/07 31/07/07 21 22 18
Nick 01/01/07 31/07/07 25 19 25
If the manager was to make an entry for Dec-06 I would need an error message to say that the entry is prior to contract start date - and therefore possibly refuse the entry. If the manager was to make an entry in Aug-07 then I would like a message as before but to allow the entry to continue (contract being extended but not yet notified)
I've tried conditional formatting but have found nothin that suites my scenario. I've also tried a hidden column whereby if the date is erronious then that cell is populated with a message - but ideally I would have like to have this column hidden.
If anyone can help or point me in another direction I would be grateful
Many thanks in advance
BTW, I'm on annual leave until 22nd October - visiting our "cousins" in the US and Canada for three weeks - New England in the fall
Chris
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")