Restrict entries according to date in another cell

My fairly simple mileage expense spreadsheet is submitted for reimbursement once each month. Cell B5 is a drop down list of months & the year (i.e November 2008) from a list on a separate sheet in the same workbook. This is used as the time frame for the travel charges being submitted. Cells in column "A" are supposed to contain the specific date within the month that the travel occurred. Because I'm such a lousy, slow typist I'd like to be able to just enter one or two digits for the specific day and have Excel extract the month abbreviated to 3 letters (i.e. Nov) using the information in the "period" cell B5. My fingers sometimes work faster than my brain and I get letters/numbers jumbled so it'd also be nice if there was some error checking that would disallow entering a 31 if the month listed in cell B5 only has 28 days. Possibly a calendar lookup?? I've searched enough and come across a lot of words/names but get bogged down in the syntax/structure.

Probably like everybody else coming to this forum, I've whiled away many an hour entering formulas in a variety of ways and searching the internet and I'm as frustrated as everybody probably is/was. And, also like most other things, it's probably not difficult if you know what you're doing. I obviously don't but it's fun in a masochistic kind of way.

Attached is the form and thanks for whatever help you give.
Ross

Hi, and welcome to the forum.

It would have been nice if the workbook had not been protected! At least it was when I opened it through an xls converter.

Why not use an additional new column A for the date in the month, e.g. '15', then the Date column could be the formula - in B8

Please Login or Register  to view this content.

If you want a check that you've not exceeded the number of days in the month, you could use a conditional format on the new date column. Alongside your list of months in col A add the days in each month, then name the range A3:B18 "MonthTable". Now use the following CF and copy it down column A of yourTemplate sheet

Please Login or Register  to view this content.

Richard,
Thanks for the reply.

Apologies about protection; I protected some cells to make it easier to tab through and so I wouldn't screw them up (again). It didn't occur to me that anybody would add columns. Have attached an unprotected copy.

Anyway, your formula sort of works, if I've done it right. I presume you meant A8 rather than E8 in "=DATE(YEAR(C5),MONTH(C5),E8)". Using A8, it does take the A8 entered number into the B8 date cell but it displays it in dd-mmm-yy format and I only want "dd mmm" (max 2 digits for days, one if the date is below 10, no hyphen, no year). This method also leaves a "duplicate" date number in the new column A. I think a large part of the problem is the MS date format requirements. Would it help if the C5 date was converted to a number?

I tried to limit the number of characters extracted from C5 using "Len" and re-arranging the year month day format but that doesn't really work because of the different number of letters in the different months. The way I tried it was, as the example showed, to subtract "x" number of characters from the right side. Don't know how to keep the first 3 characters of the month only from cell C5 (i.e. Jan, Feb, Mar, etc.)

Lastly, the formula "=A8>VLOOKUP(C5,MonthTable,2,FALSE)" entered into cell A8 doesn't prevent me from inserting any numbers. Entering 35, for instance, replaces the vlookup formula (it's gone) and adds 35 days to the C5 date so "November 2008" becomes "5-Dec-08" in cell B8.

Boy, this is getting long. If you're still with me, I appreciate it.

Ross

Hi,
The first point is just a formatting matter. On the attached example I've changed that.

I think you misunderstood the second formula. It was a Conditional Format formula, not a formula to be entered in a cell. I've added this to A8 in the example.

Short of using VBA I don't see any alternative to having an additional column if one of your requirements is to keep the entry of dates as simple and foolproof as possible.

HTH

Hi Richard,
Thought I'd answered your post hours ago but I don't see it here so I guess I didn't send it. Got too many things going at once.
Anyway.......I'm new to it but working with Excel 2007. Don't know what you're using so will send the most recently modified file as xls.

With mods, an additional "A" column works fine and I can get the look I want. As you said, the "mmmm" vs. "mmm" is just cell formatting.

You're right, I didn't/don't understand the conditional formatting but I'ma gonna learn! I've looked at this for waaay too long and I kept thinking there was something not right with the conditional formatting. Just realized that it DOES tell me there's an error, via the red highlighting, if I type in an "A" column number higher than what's allowed; it just doesn't bring everything to a crashing halt, which is what I had in mind.

Another wrench in the works- It'd be good to be able to manually enter a month ("mmm") as well; in effect, to be able to override the automatic insertion. My mileage charges are an irregular thing. While I try to log all my mileage in a book I keep in my truck, it only gets transferred to an expense sheet once each month. Experience has taught me that I make mistakes and sometimes have to make corrections after the fact (as in , the next submission).

I said I was a lousy typist. What I didn't say is that I'm a head down, lousy typist. I can't type & watch the screen at the same time. Consequently, I'm often 14 strokes ahead before I see what I've done unless there's something (like a squawk from the computer) to make me stop. You probably don't have that problem.

There is a another slight glitch, though. A zero in column A is still allowed and it causes the "mmm" in column B to regress one month.

What I've got now is better than what I've worked with for years. It just needs tweaking, and probably some VBA, to get it to do exactly what I want. For minor stuff in the past I've sometimes searched the internet for code that seems close to what and then try to adapt it to my purposes. I know what I want can be done. I don't know if I'll live long enough to be able to do it myself but I know it can be done!

You've helped. Thank you.