Date formula

[ranaray]

Hi All,


Can you please help me with this formula where;


If DOB1 = 11.11.54


if want the formula to return = 1.12.2013

Thus taking it to the 1st date of the next month in 2013


if DOB2 - 11.3.54

Formula to return = 1.4.2012

Thus taking it to the 1st date of the next month in 2012

 

[NARAYANK991]

Hi Rana ,


If your date is in A1 , and the required year in B1 , then the formula can be :


=DATE(B1,MIN(12,MONTH(A1)+1),1)


Narayan

 

[ranaray]

Thanks Narayan,


Can I not determine the year based on the month?

I dont have B1 in this situation. I have to determine B1 based on if A1 id less than June or more than June.


Thanks again

Rana

 

[NARAYANK991]

Hi Rana ,


Will this work ?


=DATE(IF(MONTH(A1)<6,2012,2013),MIN(12,MONTH(A1)+1),1)


Narayan

 

[SirJB7]

Hi, ranaray!

Another approach valid for every actual date regardless the year maybe this?:

=FECHA(AÑO(HOY());RESIDUO(MES(A1)+1;12)+(1-SIGNO(RESIDUO(MES(A1)+1;12)))*12;1) -----> in english: =DATE(YEAR(TODAY()),MOD(MONTH(A1)+1,12)+(1-SIGN(MOD(MONTH(A1)+1,12)))*12,1)

Regards!

 

[Hui]

=DATE(IF(MONTH(A1)<6,2012,2013),MONTH(A1)+1,1)


Excel is happy to have a date with Month=13+ or Day=32+ and will increment the year/month accordingly

=Date(2013,13,1) displays as 1 Jan 2014

 

[NARAYANK991]

Hi Hui ,


Yes , now I remember !


Narayan