Forum Discussion
Returning values based on Start Date / End Date in a given year
Thank you! ice12020
I have attached my notes on line 650 regarding the account Eruditus LLC, for further amendments needed - so so close!
Jenny
I have reformatted the dates in column C to numbers. So when you check line 651 (for example) you can see that the number is greater than the one in U5 which is why you don't get the result you expect.
I suspect that the date setting on your computer might be different to mine? Are you in the US?
If you go to the windows settings you should be able to see what settings you have. If they are US then the dates are likely to be in mm/dd/yy format which will give you a different number to dd/mm/yy.
Check that first and see how you get on.
Peter
Hi peteryac60
I am in UK - date settings are dd/mm/yyyy./
The issue is that 01/01/2020 is not less than 01/01/2017. So I think the formula needs tweaking.
The start date is 01/05/2019 and the End date is 01/01/2020, which means there was no contract in 2017 and so the value in 2017 column needs to be null, not Active.
This is a very tricky task, so will need to be done in steps I think!
First step is to get the right combinations of dates for the IF formula, so that it correctly populates the year columns with null or Active.
Thanks for your help!
Jenny
Look at attached - lines 650 marked in blue.
I think you had the formulas the wrong way round?
I think you should check that the start date is > end date for 2017 and if it is then mark with null , otherwise mark as Active. That makes more sense to me - hope it does to you.
if this solves your problem please mark the solution as complete.
thanks,
Peter