Excel Formula Help with Calculating Dates

[Dfuhrman8]

I have a column in a work sheet that has a Subscription Start Date, depending on that date depends on what is billed.

Here is what I am wanting, I have tried different ways of writing this out and I am not getting the output that I need. I think its when I get to the month calculation. I am not sure how to correctly write it out.

Column A = Annual Subscription Start Date

Column B = Annual Fee Paid

Column C = where my formula is going (Billing Fee)

If Current Year >A1, C1=B1, if Current Year <=A1, C1=0, if current year= A1, then(Month(A1)>6, C1=0, else C1=(B1/12)*(7-Month(A1)

Any help would be appreciated.

[#2 · Accepted solution]

@Dfuhrman8 

=B1*IF(YEAR(A1)<YEAR(TODAY()),1,IF(A1>=DATE(YEAR(TODAY()),7,1),0,(7-MONTH(A1)))/12)

or, equivalently

=B1*IF(A1<DATE(YEAR(TODAY()),1,1),1,IF(A1<DATE(YEAR(TODAY()),7,1),(7-MONTH(A1))/12,0))

[#3]

Thank you! I will test it out. Thank you again!

[#4]

That worked for Jan Billing Column, So would July Billing (7/1-12/31) look like this:

=B1*IF(Year(A1)<Year(TODAY()),1)/12), if(A1>Date(Year(Today())),8,1)/12),0,(13-Month(A1))))/12)

[#5]

@Dfuhrman8 

Please explain the rules for July billing in detail.

[#6]

For July Billing:
If current year>start date year then July Billing = Annual Fee/2
IF Current year <start date year then July billing = 0
If current year = start date year then,we look at the month of startdate,if month is <7,then July Billing =Annual Fee/2, else july billing = (Annual Fee/12)*(13-month(startdate)

[#7]

@Dfuhrman8 

Try

=B1*IF(YEAR(A1)>YEAR(TODAY()),0,IF(A1<DATE(YEAR(TODAY()),7,1),0.5,(13-MONTH(A1))/12))