looking for a different way

[viper]

hello all,

Just seeing if there can be a better way to do this. I have a cell (I4) that contains a month ending date. Then I have a cell that is hidden that contains the previous months ending date(d61). When we are ready for the new month we click a command button and it transfers the date in cell (I4) down to cell (D61) so then it becomes the previous months date. Then we enter the new month ending date in cell (I4). I do it this way because I need to compare the two dates to see if there are 4 or 5 weeks between them for our scheduling purposes. This works fine for me because I know how to use the worksheet. But the file was sent to someone else and they couldn't figure out how to work it so it filled in the dates automattically like I have it set up to based on the two in cells (I4) and (D61). Can anyone think of a different (easier) way to compare two dates? I can't, you have to have a start and end date right? My boss is telling me the way I have it now is to complicated for the people who will be using it. There is one thing wrong with it now. It will only work based on either 4 or 5 weeks.

Just looking for ideas.

 

[wi_guy]

What about a lookup table, with the period end dates in 1 column, and the # of weeks in another.....then you can always reference the current date and it will lookup the correct # of weeks for you

 

[IML]

Not sure exactly what your after, but if you have your current month end date in A1, you could use
=DATE(YEAR(A1),MONTH(A1),0)
to find the last month end date. taking it a step further,
=(A1-DATE(YEAR(A1),MONTH(A1),0))/7
would give you the number of weeks. Of course, all months except most February's will be 4.something, not a clean 4 or 5 like you say. Oh well, food for thought.

 

[viper]

haven't used one befor but good idea. I'll see if I can work it out.
Thanks,

 

[Dave Patton]

Consider the following

With a date in A2
end of month =DATE(YEAR(A2),MONTH(A2)+1,0)
end of previous =DATE(YEAR(A2),MONTH(A2),0)

Named range of months 1-12 and # of weeks
(based on your preferences)
Name the range rWeeks

Number of Weeks =LOOKUP(MONTH(A2),rWeeks)

 

[Ricky Morris]

I would use a combo box with periods 1 - 12 that the user can choose. The result from the list box could be used to obtain current and previous period end dates via a lookup table.

Let me know if this helps or if you need more specifics.