Rounding a date to the nearest half year

[Smurphster16]

Hi,

I'm looking to create a formula that will round a date to the nearest half year (either the start of the year or the middle of the year depending upon which is next)
i.e if the date was 01/08/2019 the formula would round to 01/01/2020
if the date was 03/04/3021 the formula would round to 30/06/2021

Can anyone please help me with this?

Many thanks,

Phil

 

[Joe4]

i.e if the date was 01/08/2019 the formula would round to 01/01/2020

Does it always need to round UP?
You didn't mention that, but August 1 is closer to June 30/July 1 than it is to January 1.

And are you looking for the half-year end date or half-year begin date?
You mention January 1 (which is a start date) and then June 30 (which is an end date).

 

[Smurphster16]

Hi yes in my case I would need it to always round UP

And would preferably have the start date for Jan but the end date for June but if it was just the start date for both June and Jan it would not matter too much,

Thanks

 

[Joe4]

Try this, for an entry in cell A1:

=IF(MONTH(A1)>=7,DATE(YEAR(A1)+1,1,1),DATE(YEAR(A1),6,30))

 

[Smurphster16]

This works thanks very much

 

[Joe4]

You are welcome.