PDA

View Full Version : Expiry Date Validations



derbys
02-17-2010, 02:15 PM
Hey im abit of an excel noob and trying to get a spreadsheet done for my uncle however im at a stumbling block as to where to go next (i.e im stuck solid)

Im drawing an absolute blank when its comming to validating a cell that is to hold an expiry date and am after a little help/hints. Basically what i want to happen is that the cell (H25) should hold the form mm/yy much like a credit card. Also im wanting to make sure that say if the month is out of date then a message will appear saying so, same as if a year is out of date. I've tried using the data validation option on the spreadsheet but i think its too advanced for what i want to do.

Also i dont want it to accept anything other then digits, which is proving difficult for me especially as i want to us a / in the input cell.

any help would be appreciated, im spectacularly stuck atm.

lucas
02-17-2010, 02:28 PM
If out of date, what cell determines that?

derbys
02-17-2010, 02:30 PM
Am i able to use the same cell that the data i enter in to? if so itll be h25

Like i said im pretty new to this. :D

lucas
02-17-2010, 02:47 PM
You won't be able to use the same cell.

Maybe an example will help. See attached.

There are a few notes on the sheet.

derbys
02-17-2010, 02:55 PM
Hmm i dont think thats the sort of thing that i want. It's a credit card expiry date so i want to make sure that the month and year are both valid numbers and also actually in date.

Does that make sense? having difficulties getting words out to explain it properly tonight, it seems :D

lucas
02-17-2010, 03:01 PM
are you talking about the format? Because if that's not it, I don't understand.

derbys
02-17-2010, 03:10 PM
I've added my datasheets hopefully it'll help to understand better. Under the expiry date when data is entered i want it to only accept input in the form 02/10 (mm/yy). And that it also disregards any other input so if .1/10 was entered it would flag up with an error too.

But both the month and the year need to be in date. All of this should be in the commit order macro as its attached to the commit order button.

I dont think thats any clearer :D

lucas
02-17-2010, 03:12 PM
i want to make sure that the month and year are both valid numbers.



Can you clarify this part for me?

derbys
02-17-2010, 03:18 PM
i meant that it wouldn't accept something like .1/10 as a entry. instead of say 01/10 which would be a valid entry.

lucas
02-17-2010, 03:27 PM
It might be easier to use a pop up calendar to be sure the date is entered in the correct format. Would that be something you might be interested in?

derbys
02-17-2010, 03:29 PM
personally i wouldnt mind, but its not for me its for my uncle. he wants to be just able to enter in the date like 01/10 and then just flag up if there is any error in it.

lucas
02-17-2010, 03:39 PM
Take a look at this and let me know.

just click on any of the highlighted cells and select a date.

The only cells it works on are the ones formatted for the date format you want.

derbys
02-17-2010, 03:45 PM
I do appreciate your help, but i think i would prefer it by just inserting the date itself.

sorry if im sound ungrateful - im really not! i do appreciate the help/advice :)

lucas
02-17-2010, 03:50 PM
That's ok, I'm just trying to help but I've run out of ideas.

There are some really skilled people here who will help you with this when one of them comes along.

I'm thinking maybe a regexp function may be needed.

derbys
02-17-2010, 03:54 PM
hopefully one of them notices this. but again, i do appreciate the help.. so thanks alot!

Philcjr
02-17-2010, 07:14 PM
What about Selecting Column H, Formating the cells to MM/YY... then use Data Validation to only allow "Date" Range from 01/01/1900 to 12/31/9999.

This seemed to work for me
01/01/1900 - worked
1/21/2000 - worked
.1/1/10 - did not work

was this what you were thinking of?