| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Validation - Must equal Sundays date
I have a nifty little time sheet for 3 employees to use. Unfortunatly this
thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Use datavalidationcustom and in the formula box put
=WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
I used that code and in the formula except it says:
=WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Take a look at Excel's help for =weekday(). It has a nice explanation about
what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Style is on stop, error message is there
"Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Tried it on a new spreadhseet...to make sure my other sheet wasn't messed up
and the same result happened....nothing. "jeridbohmann" wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
I'd guess you made a mistake when you typed in the formula.
I'd try again. And make sure you type in the month/day/year in the correct order. June 12, 2005 is a Sunday. (Although, April 12, 2005 is not.) I'm still guessing typo. jeridbohmann wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
What cell was active when you applied Data|Validation?
What was the formula you used? Copy and paste from that dialog. jeridbohmann wrote: Tried it on a new spreadhseet...to make sure my other sheet wasn't messed up and the same result happened....nothing. "jeridbohmann" wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
A1 is 12/04/05
Highlight Cell C1. I went to data/validation changed to custom and entered the formula =WEEKDAY(A1,2)=7 Put a Stop message on Now when I type any date (12/07/05 - - month, day, year) it lets me with no warning or stop. I tried 5 different dates. I retyped and even did a copy past of the formula Office 2003 SP2 by the way "Dave Peterson" wrote: I'd guess you made a mistake when you typed in the formula. I'd try again. And make sure you type in the month/day/year in the correct order. June 12, 2005 is a Sunday. (Although, April 12, 2005 is not.) I'm still guessing typo. jeridbohmann wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Are you using the same cell, i.e. do you select U1, then do
datavalidationallowcustom and put in the formula =WEEKDAY(U1,2)=7 -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... Tried it on a new spreadhseet...to make sure my other sheet wasn't messed up and the same result happened....nothing. "jeridbohmann" wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
If you're typing in A1, then select A1 and apply data|validation.
If you're using A1 to stop entry in C1, then as long as A1 is a Sunday, you can type anything you want in C1. Change A1 to a Saturday and you'll be stopped in C1. jeridbohmann wrote: A1 is 12/04/05 Highlight Cell C1. I went to data/validation changed to custom and entered the formula =WEEKDAY(A1,2)=7 Put a Stop message on Now when I type any date (12/07/05 - - month, day, year) it lets me with no warning or stop. I tried 5 different dates. I retyped and even did a copy past of the formula Office 2003 SP2 by the way "Dave Peterson" wrote: I'd guess you made a mistake when you typed in the formula. I'd try again. And make sure you type in the month/day/year in the correct order. June 12, 2005 is a Sunday. (Although, April 12, 2005 is not.) I'm still guessing typo. jeridbohmann wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
A1 is 12/04/05
Highlight Cell C1. I went to data/validation changed to custom and entered the formula =WEEKDAY(A1,2)=7 Put a Stop message on Now when I type any date (12/07/05 - - month, day, year) it lets me with no warning or stop. I tried 5 different dates. I retyped and even did a copy paste of the formula C1 is the one I want to type in and get a stop message. "Dave Peterson" wrote: What cell was active when you applied Data|Validation? What was the formula you used? Copy and paste from that dialog. jeridbohmann wrote: Tried it on a new spreadhseet...to make sure my other sheet wasn't messed up and the same result happened....nothing. "jeridbohmann" wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Select A1, not C1, you need apply validation to the cells that you enter the
dates in -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... A1 is 12/04/05 Highlight Cell C1. I went to data/validation changed to custom and entered the formula =WEEKDAY(A1,2)=7 Put a Stop message on Now when I type any date (12/07/05 - - month, day, year) it lets me with no warning or stop. I tried 5 different dates. I retyped and even did a copy past of the formula Office 2003 SP2 by the way "Dave Peterson" wrote: I'd guess you made a mistake when you typed in the formula. I'd try again. And make sure you type in the month/day/year in the correct order. June 12, 2005 is a Sunday. (Although, April 12, 2005 is not.) I'm still guessing typo. jeridbohmann wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Bingo! I am a total retard!
What I thought was A1 was referring to the start date to use. I am sorry...long day, brain isn't working properly I guess. Thank you and Thank you Dave!!! "Peo Sjoblom" wrote: Are you using the same cell, i.e. do you select U1, then do datavalidationallowcustom and put in the formula =WEEKDAY(U1,2)=7 -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... Tried it on a new spreadhseet...to make sure my other sheet wasn't messed up and the same result happened....nothing. "jeridbohmann" wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Validation - Must equal Sundays date
Sorry guys...kicking myself here for not taking a deep breath and going
slow...misread it. Thank yo uso much for your patience! "Peo Sjoblom" wrote: Select A1, not C1, you need apply validation to the cells that you enter the dates in -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... A1 is 12/04/05 Highlight Cell C1. I went to data/validation changed to custom and entered the formula =WEEKDAY(A1,2)=7 Put a Stop message on Now when I type any date (12/07/05 - - month, day, year) it lets me with no warning or stop. I tried 5 different dates. I retyped and even did a copy past of the formula Office 2003 SP2 by the way "Dave Peterson" wrote: I'd guess you made a mistake when you typed in the formula. I'd try again. And make sure you type in the month/day/year in the correct order. June 12, 2005 is a Sunday. (Although, April 12, 2005 is not.) I'm still guessing typo. jeridbohmann wrote: Style is on stop, error message is there "Dave Peterson" wrote: Take a look at Excel's help for =weekday(). It has a nice explanation about what that number means. When you applied data|Validation, did you check the "show error alert..." box on the Error Alert tab? Make sure you make the Style "stop", too. jeridbohmann wrote: I used that code and in the formula except it says: =WEEKDAY(U1,2)=7 In cell U1 I have 12/04/05 Is this correct? I enter 12/06/05 and it take it and every other date I try. I must be missing something. Also: U1 = the cell, what does the ",2" do for the formula? I put that message in there too! Maybe he'll get the picture! Thanks ! "Peo Sjoblom" wrote: Use datavalidationcustom and in the formula box put =WEEKDAY(A1,2)=7 where A1 is the cell with the validation, click the error alert tab and type a message like "John, learn the difference between Sunday and other days!" -- Regards, Peo Sjoblom "jeridbohmann" wrote in message ... I have a nifty little time sheet for 3 employees to use. Unfortunatly this thing needs to be dummy proof because now I am responsible of making sure they enter the right dates. All they need to do is enter every other weeks start date. For instance: John's pay period starts on 12/4/05 (a sunday). All he has to do is type in 12/4/05 in one cell and the spreadsheet fills in the end date, the weekday dates, etc.. He won't have to do it again until 2 weeks (12/18/05). John can't seem to enter the right date and then payroll gets mad and they yell at me...blah blah. So I tried doing a data validation for a range of cells (I have all the valid Sundays in a column on the same sheet). What am I doing wrong?? Is this even possible? -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Another Date issue. | Excel Worksheet Functions | |||
| Return Sundays date of current week | Excel Worksheet Functions | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date. | Excel Discussion (Misc queries) | |||
Linear Mode
