Restrict date to Month, year only

[Peter Rattigan]

I am building a system which captures data in Access2K and uses Transferspreadsheet to export data to Excel where it is processed.

I have a situation where I need to create a vlookup in Excel using a date field as the lookup. I only want users to input the date as Month year, the day is not needed. In this way I can compare the date columns and know that the dates will match.

I have tried various formattings in Access to make the date entered = Month year but they all display a full date as the underlying base date ie Nov 2005 (wanted) may be stored as 5/11/05 (not wanted). When I compare that with the lookup table the dates may not match.

Is it possible in both Access and Excel to achieve a date input of Month and year only. If so how?


Thanks

 

[RalphA]

Try entering 'Nov 2005

 

[Peter Rattigan]

Thanks RalphA

Will your idea work with date functions such as edate?


Cheers
Peter

 

[RalphA]

Peter, I have only tried to solve the problem you posed, namely:

I have a situation where I need to create a vlookup in Excel using a date field as the lookup. I only want users to input the date as Month year, the day is not needed. In this way I can compare the date columns and know that the dates will match. [/unquote]

Your new question, on whether the results from my proposed solution will work or not on a specific function, or, even, on other functions, without even trying them out, is a logical "NO!". Excel formulas use mathematics to do their thing. And, a date is treated as the number of days elapsed since a pre-selected date, such as Januray 1, 1900.

However, if you have 'Nov 2005 in, say, cell B2, and you enter =VALUE(B2) in, say, cell C2, you will see the answer as 38657. With this knowledge, I'm, sure you can now use my 'Nov 2005 proposed solution.

For instance, if I have 'Nov 2005 in B2, and 'Oct 2005 in B3, if I enter, in B4, =VALUE(B2)-VALUE(B3), I see 31-Jan in B4, and, formatting B4 as 03/04/97 (for Excel 97), I now see, 01/31/00, or 31 days difference. And, if I format Br as Number with no decimals, I see 31 as the answer.

Hopefully, with the above, you will be able to understand how to take advantage of my proposed 'Nov 2005 for proper use in your formulas. And, remember, If at first you don't succeed, try, and try again. That is, DO try one thing or another, until you either get your answer, or run out of what to do next, in which latter case, do come back here with your questions.

 

[Peter Rattigan]

Thanks for your advice and help.

Cheers