Next January 1 or July 1
 

Hi, I was looking for a cell formula that returns the next January 1 or July 1 after a given date.

Examples:

3/8/2013 would return 7/1/2013

or

8/21/2014 would return 1/1/2015

Here is a formula for returning the first day of the next quarter (provided by "Bernd P") - but I wasn't able to tweak it to return the next Jan. 1 or July 1:

=DATE(YEAR(A1),1+FLOOR(MONTH(A1)+2,3),1)

Thanks.

Next July 1 or January 1
 

Hi, I was looking for a cell formula to return the next July 1 or
January 1 (whichever falls first) after a given date.

Examples:

3/8/2013 would return 7/1/2013

or

8/21/2014 would return 1/1/2015

Any ideas?

Thanks!

Assuming the date is in colA, and the cell containing this formula is
'Date' format...

=IF(MONTH(A1)<7,DATE(YEAR(A1),7,1),DATE(YEAR(A1)+1 ,1,1))

Perhaps, though, our local formula wizard Claus will have something
better...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks very much Garry.

Next July 1 or January 1
 

Hi Garry,

Am Tue, 23 Sep 2014 19:49:55 -0400 schrieb GS:

=IF(MONTH(A1)<7,DATE(YEAR(A1),7,1),DATE(YEAR(A1)+1 ,1,1))

Perhaps, though, our local formula wizard Claus will have something
better...

;-)
There are other ways but none so short and clear than yours


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Next July 1 or January 1
 

Hi Elizabeth,

Am Tue, 23 Sep 2014 21:15:14 +0100 schrieb ElizabethWells:

3/8/2013 would return 7/1/2013

or

8/21/2014 would return 1/1/2015

try:
=DATE(YEAR(A1),IF(MONTH(A1)<7,7,13),1)
or
=EDATE(A1,IF(MONTH(A1)<7,7,13)-MONTH(A1))-(DAY(A1)-1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Next July 1 or January 1
 

'GS[_2_ Wrote:
;1618696'] Hi, I was looking for a cell formula to return the next
July 1 or-
January 1 (whichever falls first) after a given date.

Examples:

3/8/2013 would return 7/1/2013

or

8/21/2014 would return 1/1/2015

Any ideas?

Thanks!-

Assuming the date is in colA, and the cell containing this formula
is 'Date' format...

=IF(MONTH(A1)<7,DATE(YEAR(A1),7,1),DATE(YEAR(A1)+1 ,1,1))

Perhaps, though, our local formula wizard Claus will have something
better...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks very much Garry.

You're welcome! Glad to be of help...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Next July 1 or January 1
 

Hi Garry,

Am Tue, 23 Sep 2014 19:49:55 -0400 schrieb GS:

=IF(MONTH(A1)<7,DATE(YEAR(A1),7,1),DATE(YEAR(A1)+1 ,1,1))

Perhaps, though, our local formula wizard Claus will have something
better...

;-)
There are other ways but none so short and clear than yours


Regards
Claus B.

I like to reflect the logic in my formulas, though I do hate using cell
addresses! You have a knack for brevity that I also like, but I find
using named refs easier for users to understand what a formula does!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Next July 1 or January 1
 

Hi Garry,

Am Wed, 24 Sep 2014 08:56:37 -0400 schrieb GS:

I like to reflect the logic in my formulas, though I do hate using cell
addresses! You have a knack for brevity that I also like, but I find
using named refs easier for users to understand what a formula does!

I don't know anything about the knowledge and the preferences of the OP.
And I guess it is easier for a specialist to change the cell addresses
with range names than for a layman to change names to cell addresses.
For myself I use names also.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Next July 1 or January 1
 

Hi Garry,

Am Wed, 24 Sep 2014 08:56:37 -0400 schrieb GS:

I like to reflect the logic in my formulas, though I do hate using
cell addresses! You have a knack for brevity that I also like, but
I find using named refs easier for users to understand what a
formula does!

I don't know anything about the knowledge and the preferences of the
OP. And I guess it is easier for a specialist to change the cell
addresses with range names than for a layman to change names to cell
addresses. For myself I use names also.


Regards
Claus B.

Typically, names reflect headers so users know which col is being
ref'd. Usually these names are col-absolute/row-relative.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks to you too Claus!
BTW, this helps determine correct plan entry for those plans (401(k), profit sharing etc.) that have those two entry dates. Thanks again!

Next July 1 or January 1
 

On Tue, 23 Sep 2014 21:15:14 +0100, ElizabethWells wrote:


Hi, I was looking for a cell formula to return the next July 1 or
January 1 (whichever falls first) after a given date.

Examples:

3/8/2013 would return 7/1/2013

or

8/21/2014 would return 1/1/2015

Any ideas?

Thanks!

And another method:

=DATE(YEAR(A1),INT((MONTH(A1)-1)/6)*6+7,1)