Hey all, and it’s time for another Formula Friday….

Today I want to share with you a query recently sent to me by Daniel. He wanted to calculate how many days he had left in a campaign. Simple enough to begin with as we can calculate the difference between today’s date and the end date of the campaign. But what if the campaign had not yet started…..ah hah, I feel an IF formula approaching…..

The logic in a nutshell is—

If the campaign start date after today then count the number of days between the start and the end date but if the start date of the campaign is before today’s date then count the number of days between today and the end of the campaign.

So let’s get Excel to work this out for us using IF and DATEDIF using an example.

I have a few campaigns in my example above, by using the IF function I test two conditions a true and a false. In this example I want to test if the start of the campaign if after today

=IF(D5>TODAY()- this is the first part of the formula

The second part displays the difference in days between the start and end date of the campaign IF the campaign has not yet started i.e if the condition in the first part of the formula is TRUE.

DATEDIF(D5,F5″D”)

The third part of the formula will display the difference in days between today and the end of the campaign if the start date of the campaign is not after today i.e if the condition is FALSE.

DATEDIF(TODAY(),F5,”D”))

So in my campaigns above

• Blue Moon is starting today so the difference between start and end date is 33 days
• Yellow Carpet is not starting until 01/04/2015 and ends on the 01/08/2015 so will run for 122 days
• Red Car started on 01/01/2015 and finishes tomorrow on the 28/02/2015 therefore there is 1 day left in the campaign

Formula Friday- How To Pick A Winner Using Excel
Formula Friday- Find The k-th Largest Value In Your Excel Data Set Using The LARGE Function
Formula Friday- Use the LEN Formula To Highlight Issues With My Data Import