Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: looking for a different way

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    haven't used one befor but good idea. I'll see if I can work it out.
    Thanks,
    I appreciate the help from everyone at Mr. Excel.

    viper

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default


    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)

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    It's never too late to learn something new.

    Ricky

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •