IF statements to calculate what year a customer joined and left in

[mitch992]

So I've been wrecking my brain on something i feel should be quite simple.

I basically have an excel spreadsheet which lists customers, the start date of the contract with us and then end date (or no end date if they are still a customer now).

I need to work out, for each financial year from 2017 to 2019:

How many customers there where at the begining of each of those periods, and how many there are at the end of those periods.

I've tried using nestedifs but it doesnt seem to return the right values. I'm bascially looking to find out the below

- Were they a customer before this start date and still one by the end of this date?
- if they were not a customer before the start date did they join during the year?

[grasstoe]

Try adding a sample spreadsheet that will often spead up response time and help to focus your question.
Cheers
grasstoe

[protonLeah]

thank you for modifying your title

[mitch992]

Hi,

So the spreadsheet looks like this (not sure how to embedd)

Client StartDate EndDate FY2016 FY2017 FY2018
X Dec-18 Mar-19

I want to populate the financial years with whether they were a customer during each period (FY16 will be 1st April 2015 to 31st March 2016 etc.) and to just show a 1 if they were.

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!