Output a date if another date is a certain value

[fandiwany]

Hi there,


I have a list of payments coming in, and the date they arrived. So for example in 2017-18 I have

30 Mar 2017 Mr Smith $1000
30 Apr 2017 Mr Jones $1000
30 Nov 2017 Mr White $1000
30 Jan 2017 Mr Brown $1000

In a separate column called "Apply to period ending:" it should say "1 Feb 2018" for all these payments (since they were paid between 2nd Feb 2017 and 1 Feb 2018).

I can't figure out how to do this, can anyone help?

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[fandiwany]

Here it is

[fandiwany]

And here's what I want it to look like but with me having to copy and paste it for a whole list of figures with different years

[samba_ravi]

d2=DATE(YEAR(A2-32)+1,2,1)
Try this and copy towards down
In A3 and A4 Names are associated with Dates please remove them to get result otherwise will get error
30 Apr 2017 Mr Jones
30 Nov 2017 Mr White

[fandiwany]

That works perfectly - but I have absolutely no idea why!

Could you explain how it works? Many thanks for the solution

[fandiwany]

Also it is not working if the payment date was Feb 1st.

[samba_ravi]

Also it is not working if the payment date was Feb 1st.

you said that only the dates between 02-02-2017 and 01-02-2018 to be displayed as 01-02-2018
for 01-02-2018 it will show as 01-02-2018
for 01-02-2017 it will show as 01-02-2017

[samba_ravi]

wrong post

[samba_ravi]

That works perfectly - but I have absolutely no idea why!

Could you explain how it works? Many thanks for the solution

---

in the given case
1 Feb 2018 to be shown for all the days between 2nd Feb 2017 and 1 Feb 2018.
in this case total 32 days are in the year 2018 and remaining are in 2017 so if you deducted 32 from any dates between 2-02-2017 and 01-02-2018 it will be always in the year 2017 only, so I am adding 1 to the year 2017 (2017+1=2018)

[fandiwany]

Ah yes I understand now. I couldn't see what the 32 was doing. Genius!

धन्यवाद!