# Thread: Date Manipulation ? (Excel 2002/2003)

1. ## Date Manipulation ? (Excel 2002/2003)

Hi

I have a problem with manipulating dates.

If anyone can help I would be most grateful. Please see attached

2. ## Re: Date Manipulation ? (Excel 2002/2003)

You want a formula to return the 1st of February on or after the date specified in D10. Is that correct? If so, use
<code>
=DATE(YEAR(D10)+(D10>DATE(YEAR(D10),2,1)),2,1)
</code>
Otherwise, please provide a more clear explanation or more examples.

3. ## Re: Date Manipulation ? (Excel 2002/2003)

re> You want a formula to return the 1st of February on or after the date specified in D10. Is that correct? If so, use

=DATE(YEAR(D10)+(D10>DATE(YEAR(D10),2,1)),2,1)

This not quite what I am looking for

I will try to give a better example

If the date entered = Jan 2006 then the example below would read Prices From 01-Nov-06 to 01-Feb-08, when the new prices come into force.
ie jan 06 to Jan 07 12 months plus Jan 07 1 month 13 months total

If the date entered was March 2006 it would only need to last 12 months because it would run through Feb 2007 when the prices increased

Prices From 01-Nov-06 To 01-Nov-07 this only calculates for 12 months

Regards

4. ## Re: Date Manipulation ? (Excel 2002/2003)

I'm afraid I don't understand.

5. ## Re: Date Manipulation ? (Excel 2002/2003)

HI Hans

What I am trying to do here is, as we have price increases every Feb, If a customer signs a contract in Jan we couldn't increase prices after one month it would have to be the following Feb ie 13 months

or if he signed up in November we couldn't increase prices after 3 months. There has to be minimum of 12 months between signing and price increases.

But if he singed in March 12 months hence would include the next Feb price increase.

Sorry to be so tiresome.

Best Regards

6. ## Re: Date Manipulation ? (Excel 2002/2003)

Hans,
I understand what Braddy is after, but I cannot get the function working....I know I am using Date incorrectly, but let the same formula just give you a starting point...

=IF(AND(MONTH(D10)>=2,MONTH(D10)<=10),DATE(D10,D10 +12,D10),IF(MONTH(D10)=11,DATE(D10,D10+15,D10),IF( MONTH(D10)=12,DATE(D10,D10+14,D10),DATE(D10,D10+13 ,D10))))

7. ## Re: Date Manipulation ? (Excel 2002/2003)

Does this do what you want?
<code>
=DATE(YEAR(D10),MONTH(D10)+12+(MONTH(D10)=1)+3*(MO NTH(D10)=11)+2*(MONTH(D10)=12),1)</code>

8. ## Re: Date Manipulation ? (Excel 2002/2003)

That looks like it does it...

Nice formula...if you get change would you care to explain it...
Tx

9. ## Re: Date Manipulation ? (Excel 2002/2003)

Hi Hans

If you were British you would surely get a knighthood, or maybe an honourary one <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Works just fine, like Rudi I'm not sure how it works but it surely does.

Regards

10. ## Re: Date Manipulation ? (Excel 2002/2003)

> if you get change

How much spare change do you have? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

=DATE(YEAR(D10),MONTH(D10)+12+(MONTH(D10)=1)+3*(MO NTH(D10)=11)+2*(MONTH(D10)=12),1)

The DATE function has arguments year, month, day.
For year we take the year of the date entered in D10, and since Braddy wants the 1st of the month, we use 1 as day.
For the month, we start by adding 12 to the month of the date in D10. We could also have added 1 to the year, that has the same effect, but I wanted to stay close to Braddy's instructions.
The further additions to month use the fact that TRUE = 1 and FALSE = 0 in Excel. So, for example, MONTH(D10)=11 evaluates to 1 for a date in November, and to 0 for other months.
Hence, 3*(MONTH(D10)=11) evaluates to 3 for November, and to 0 for other months. The total addition for a date in November is 12+3 = 15, as requested. Similar for the other 'special' months December and January.

11. ## Re: Date Manipulation ? (Excel 2002/2003)

Hi Hans

Just a rider to this how do I adjust the formula to show nothing if D10 is blank?

Many Thanks

12. ## Re: Date Manipulation ? (Excel 2002/2003)

<code>=IF(D10="","",previous formula without the =)</code>

13. ## Re: Date Manipulation ? (Excel 2002/2003)

Hi Hans

Thanks again.

14. ## Re: Date Manipulation ? (Excel 2002/2003)

How much spare change...... uhm...about <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>....
I notice you dished out the explanation for free though....very kind. (However, you will not get far if you made a business of it!!!) - <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Tx for the clear and concise breakdown...it makes good cents <img src=/S/grin.gif border=0 alt=grin width=15 height=15> now....
Cheers

#### Posting Permissions

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