[ElizabethWells]

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.

[GS[_2_]]

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

[ElizabethWells]

Originally Posted by GS[_2_]

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.

[Claus Busch]

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

[Claus Busch]

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

[GS[_2_]]

'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

[GS[_2_]]

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

[Claus Busch]

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

[GS[_2_]]

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

[ElizabethWells]

Originally Posted by Claus Busch

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

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!

[Ron Rosenfeld[_2_]]

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)