Manipulating dates

[Guest]

When I apply this formula - =EOMNTH(E28,3) - to a date Jan-08 in cell E28
Excel gives me the correct answer Apr-08. When I use the same approach in an
IF statement it doesn't work. Here's the formula I'm using -
=IF(E28<=$B$78,E28,IF(E28-$B$78<6,EOMONTH(E28,3),E28))

$B$78 is TODAY - Oct-07

Can any body help

 

[Peo Sjoblom]

It's not the same approach

E28-$B$78<6

that part compares the days, are you really asking if E28-today() is less
than 6 days, that means that
if you have 01/01/08 in E28 it will be FALSE and the result should be E28.
It looks to me if you want to check months and not days so that means this
formula might work

=IF(E28<=TODAY(),E28,IF(DATEDIF(TODAY(),E28,"m")<6,EOMONTH(E28,3),E28))


note that you can skip the B78 altogether and use TODAY() instead


also note that EOMONTH returns end of the month 3 months after, if you want
3 months after look at EDATE in help

EDATE(E28,3)

and if the date in E28 is Jan 1 2008 it will return Apr 1 2008


--


Regards,


Peo Sjoblom

 

[Stephen]

When I apply this formula - =EOMNTH(E28,3) - to a date Jan-08 in cell E28
Excel gives me the correct answer Apr-08. When I use the same approach in
an
IF statement it doesn't work. Here's the formula I'm using -
=IF(E28<=$B$78,E28,IF(E28-$B$78<6,EOMONTH(E28,3),E28))

$B$78 is TODAY - Oct-07

Can any body help

The formula does work. I suspect you may not understand Excel dates and how
they calculate.

Excel stores dates as the number of days since 31st December 1899; (that is,
1st Jan 1900 = 1). What you display depends on the formatting you apply. So,
if you format today (26th Oct 2007, or serial number 39381) as mmm-yy, you
display Oct-07. The important point is that formatting doesn't change the
underlying number.

Now because the dates are stored thus (number of days since 31st December
1899), when you do arithmetic, you are calculating in days. Even if you are
displaying only month/year, the calculation is still in days. So the
difference between your date in Jan 2008 and today is (much) greater than 6.

If you want to calculate the difference in months, you need to extract
information from your date cells (using YEAR(E28), MONTH(E28), etc.) and
incorporate this into your formula.

 

[Guest]

When I apply this formula - =EOMNTH(E28,3) - to a date Jan-08 in cell E28
Excel gives me the correct answer Apr-08. When I use the same approach in an
IF statement it doesn't work. Here's the formula I'm using -
=IF(E28<=$B$78,E28,IF(E28-$B$78<6,EOMONTH(E28,3),E28))

$B$78 is TODAY - Oct-07

Can any body help

Thank you. I stupidly forgot the answer was in days. By changing the 6 to
180 I got the right answer.

 

[Niek Otten]

Hi Peter,

I hope you realize that that is a rather inaccurate solution

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
|
| "Peter" wrote:
|
| > When I apply this formula - =EOMNTH(E28,3) - to a date Jan-08 in cell E28
| > Excel gives me the correct answer Apr-08. When I use the same approach in an
| > IF statement it doesn't work. Here's the formula I'm using -
| > =IF(E28<=$B$78,E28,IF(E28-$B$78<6,EOMONTH(E28,3),E28))
| >
| > $B$78 is TODAY - Oct-07
| >
| > Can any body help
|
| Thank you. I stupidly forgot the answer was in days. By changing the 6 to
| 180 I got the right answer.
|

 

[Peo Sjoblom]

Not necessarily, 180 days is approximate for instance 180 days added to Jul
1 07 will return 12/28/07

with 07/01/07 in A1

=A1+180

returns 12/28/07

whereas

=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

returns 01/01/08



--


Regards,


Peo Sjoblom