Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Formula creation (Excel 2003)

    I need to create a formula that each month takes a previous month gives the date as yyyymmdd appends another number that changes with the month and then adds a constant before all of this and after all of this.
    For instance for the month of may ie this month the cell would show 0000 0 *20050430* 0001003031 *3* 4000001217 5902 6
    0000 0 is constant
    20050430 is the date of the previous month
    0001003031 is a constant
    3 is a variable that is dependent on the date ie Jan should show 7, Feb 0 , March 5 April 1, may 3, June 9, July 1 August 0, Sept 6 Oct 1 Nov 7 and the value for Dec is 9
    4000001217 5902 6 is a constant
    This is used for inventory purposes. Not certain why the month variable are as above but for the computer to ocr this number correctly i need these monthly values.
    This is so i can generate forms that can be scaned vs using preprinted forms that I have to type into.
    Any help appreciated
    Thanks
    Jr

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula creation (Excel 2003)

    I would store each of the parts in a separate column, and a small lookup table to get the number corresponding to the month. The total value can be assembled by concatenation (using &).

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula creation (Excel 2003)

    Taking Hans's suggestion, I created a demo for you to view/use!
    Cheers
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Formula creation (Excel 2003)

    Rudi and Hans that is great, many thanks and esp thanks for the spreadsheet.
    Can I create a double lookup where it takes todays date ie the now function.
    So if now = may it would take the april date function
    if now was say march 3 2005 then it would find the febraury function.?
    Jerome

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula creation (Excel 2003)

    I'm not too sure what you mean by a double lookup?

    You can take the =Today() function and minus 30 days, ie =TODAY()-30 which will give you last months date in a more volatile way.
    PS: If you need more accuracy, we will need to use a function to work out how many days last month had!

    Am I on the right track with the suggestions?
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Formula creation (Excel 2003)

    Rudi:
    This is dead on but i wanted to automate it. So that it woutd take Today() know that it was the month of may subtract one month from it and then know that it was april and then give the 0000 0 *20050430* 0001003031 *1* 4000001217 5902 6
    If today was month of feb then it would give the function 0000 0 *20050228* 0001003031 *0* 4000001217 5902 6
    The function always gives the last day of the previous month.vs todays date
    Hope I have not made it too complicated
    Jerome

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula creation (Excel 2003)

    The Analysis ToolPak contains a function EOMONTH that can be used for this. (Install it using Tools | Add-Ins... if necessary)

    =EOMONTH(TODAY(),-1)

    returns the last day of last month. If you don't want to use the Analysis ToolPak, you can use

    =DATE(YEAR(TODAY()),MONTH(TODAY()),0)

    (The 0th day of this month is the last day of last month)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula creation (Excel 2003)

    How about February?

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Formula creation (Excel 2003)

    Rudi and Hans:
    This is really really great.
    Many many thanks I can now generate the forms based on todays today to give the needed output
    Jerome

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula creation (Excel 2003)

    <P ID="edit" class=small>(Edited by Rudi on 04-May-05 10:56. Added new attachment to correct last day of month!!!)</P>Thanx, thats clear now!

    There is the new demo where I modified cell B2 to always contain the 30th day of last month!
    Regards,
    Rudi

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula creation (Excel 2003)

    Yes, I suddenly spotted what I did, and had to correct that. It is now done in the new attachment!
    Cheers
    Regards,
    Rudi

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula creation (Excel 2003)

    Wow Hans, I learned something now. Thats easier to remember than the formula I used. 0 = last months last day....great!!!
    Regards,
    Rudi

Posting Permissions

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