# Thread: Formula creation (Excel 2003)

1. ## 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. ## 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. ## Re: Formula creation (Excel 2003)

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

4. ## 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. ## 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?

6. ## 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. ## 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)

9. ## 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. ## 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!

11. ## 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

12. ## 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!!!

#### Posting Permissions

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