SOLVED

Excel Formula Help with Calculating Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-3300754%22%20slang%3D%22en-US%22%3EExcel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300754%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20in%20a%20work%20sheet%20that%20has%20a%20Subscription%20Start%20Date%2C%20depending%20on%20that%20date%20depends%20on%20what%20is%20billed.%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20am%20wanting%2C%20I%20have%20tried%20different%20ways%20of%20writing%20this%20out%20and%20I%20am%20not%20getting%20the%20output%20that%20I%20need.%20I%20think%20its%20when%20I%20get%20to%20the%20month%20calculation.%20I%20am%20not%20sure%20how%20to%20correctly%20write%20it%20out.%3C%2FP%3E%3CP%3EColumn%20A%20%3D%20Annual%20Subscription%20Start%20Date%3C%2FP%3E%3CP%3EColumn%20B%20%3D%20Annual%20Fee%20Paid%3C%2FP%3E%3CP%3EColumn%20C%20%3D%20where%20my%20formula%20is%20going%20(Billing%20Fee)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Current%20Year%20%26gt%3BA1%2C%20C1%3DB1%2C%20if%20Current%20Year%20%26lt%3B%3DA1%2C%20C1%3D0%2C%20if%20current%20year%3D%20A1%2C%20then(Month(A1)%26gt%3B6%2C%20C1%3D0%2C%20else%20C1%3D(B1%2F12)*(7-Month(A1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3300754%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300773%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300773%22%20slang%3D%22en-US%22%3EThank%20you!%20I%20will%20test%20it%20out.%20Thank%20you%20again!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300764%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DB1*IF(YEAR(A1)%3CYEAR%3E%3DDATE(YEAR(TODAY())%2C7%2C1)%2C0%2C(7-MONTH(A1)))%2F12)%3C%2FYEAR%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%2C%20equivalently%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DB1*IF(A1%3CDATE%3E%3C%2FDATE%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344127%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344127%22%20slang%3D%22en-US%22%3EThat%20worked%20for%20Jan%20Billing%20Column%2C%20So%20would%20July%20Billing%20(7%2F1-12%2F31)%20look%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DB1*IF(Year(A1)%3CYEAR%3EDate(Year(Today()))%2C8%2C1)%2F12)%2C0%2C(13-Month(A1))))%2F12)%3C%2FYEAR%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344254%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20explain%20the%20rules%20for%20July%20billing%20in%20detail.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344293%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344293%22%20slang%3D%22en-US%22%3EFor%20July%20Billing%3A%3CBR%20%2F%3EIf%20current%20year%26gt%3Bstart%20date%20year%20then%20July%20Billing%20%3D%20Annual%20Fee%2F2%3CBR%20%2F%3EIF%20Current%20year%20%3CSTART%20date%3D%22%22%20year%3D%22%22%20then%3D%22%22%20july%3D%22%22%20billing%3D%220%26lt%3BBR%22%3E%3C%2FSTART%3EIf%20current%20year%20%3D%20start%20date%20year%20then%2Cwe%20look%20at%20the%20month%20of%20startdate%2Cif%20month%20is%20%26lt%3B7%2Cthen%20July%20Billing%20%3DAnnual%20Fee%2F2%2C%20else%20july%20billing%20%3D%20(Annual%20Fee%2F12)*(13-month(startdate)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344517%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Help%20with%20Calculating%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%3DB1*IF(YEAR(A1)%26gt%3BYEAR(TODAY())%2C0%2CIF(A1%3CDATE%3E%3C%2FDATE%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

6 Replies
best response confirmed by Grahmfs13 (Microsoft)
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))

Thank you! I will test it out. Thank you again!
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)

@Dfuhrman8 

Please explain the rules for July billing in detail.

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)

@Dfuhrman8 

Try

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