Excel Date calculation

0

I need to calculate prorated days for real estate closings automatically for the tax prorations. I have everything figured out except I have to manually enter the prorated date. For example, house closes june 1st, it will always calculate days until june 30th. I simply have a formula subtracting june 30th from june 1st to give me number of days, however, i have to constantly monitor the 6/30 date to make sure the year is the following june 30th, I'd like to automate this. how do i enter a formula that says I want this cell to say 6/30/(after todays date)? so, if today is 8/23/19 I want the prorated date to read 6/30/2020. If it were say, 4/30/19, I want the prorated date to read 6/30/19, so always the june 30th after whatever date.

Severin

Posted 2019-08-23T15:03:12.383

Reputation: 1

So you want the prorated date to always be the next 30th June after the date you put in? – Smock – 2019-08-23T15:17:31.607

If you want it to do it automatically in the cell after you type, it will need to be in vba. – Scott Craner – 2019-08-23T15:22:57.493

1So if the input date is 6/30/2020, you want the output date to be 6/30/2021 ??? – Gary's Student – 2019-08-23T15:23:20.503

Answers

2

With the input date in A1, in B1 enter:

=IF(A1<DATE(YEAR(A1),6,30),DATE(YEAR(A1),6,30),DATE(YEAR(A1)+1,6,30))

enter image description here

Gary's Student

Posted 2019-08-23T15:03:12.383

Reputation: 15 540

Just seen that 30th June calculates to the next year - This works better and it's neater +1 – Smock – 2019-08-23T15:41:18.783

1@Smock If 30th June does not roll-over, we would replace < with <= – Gary's Student – 2019-08-23T15:45:39.757

I think the 6/30 is arbitrary. The idea is that when the user puts in a date it does not automatically assume current year. – Scott Craner – 2019-08-23T15:45:48.710

@ScottCraner it does matter if it's before 6/30 - that's why it's important - 'If it were say, 4/30/19, I want the prorated date to read 6/30/19' <- 30th of June in the same year – Smock – 2019-08-23T15:52:04.910

No, I think the user will put in the sale date. 6/30 was only an example not a fixed date. @Smock Not every home will sell on the 1 of july. – Scott Craner – 2019-08-23T15:53:18.040

'so always the june 30th after whatever date'. He's quite clear with his 4/30/19 example too. – Smock – 2019-08-23T15:55:13.960

@Smock "*For example*, house closes june 1st, it will always calculate days until june 30th." Emphasize mine – Scott Craner – 2019-08-23T15:57:27.207

@Smock but I will concede I may be wrong. Just stating my opinion of the question – Scott Craner – 2019-08-23T15:58:34.327

2Or slightly shorter version: =DATE(YEAR(A1)+(A1>DATE(YEAR(A1),6,30)),6,30) – fixer1234 – 2019-08-23T21:59:50.393

I think the bench mark is 06/30 and the Year changes +1 if input is after date. Also operator should <= will protect if Input date is 06/30/2019 or even A1> will also works. – Rajesh S – 2019-08-24T09:34:26.067

Asked: 2019-08-23T15:03:12.383

Viewed: 44 times

Active: 2019-08-23T15:42:03.270