Fixed Annual/Semi-Annual Bills

m.salah2405

m.salah2405

New member
Joined
Dec 5, 2014
Messages
7
Reaction score
0
Points
0
Dear all,

We have 2 fixed Billing cycles
1-Annual on 07.February of each year (covers from 07.Feb Till 06.Jan of next year)
2- Semi-Annual on 07.February (2 times a year From 07.Feb. Till next 06.Aug and then 07.August Till 06.Feb)
am trying to find find formulas to get the FIRST Bill cycle date and LAST Bill Cycle date based upon start date of service and last date of service


A
B
C
D
E
F
Service Name
Start Date
1st Billed on
End Date
Last Billed on
Bill Cycle Type
DIA
05-Feb-2011
07-Feb-2011
25-Dec-2013
07-Feb-2012
Annual
VPN
14-Jul-2009
07-Aug-2009
25-Apr-2014
07-Feb-2014
Semi-Annual

Can we have these formulas in C and E, as am building other calculations based on these formulas?

Appreciate your help,, It's OK if there's a way with VB and formulas did not workout
 
What determines if it is annual or semi-annual?
 
Any ideas? I tried roundup formulas but all failed,,
 
Hi, try this. I think I have it right.
I'm just hoping that the 1st date in column E was actually mean't to be 7-Feb-13.
 

Attachments

  • Annual Bills.xlsx
    10.8 KB · Views: 8
I've added data validation drop-downs to Column F Just as a suggestion for you, might be quicker and save typo's.
 

Attachments

  • Annual Bills.xlsx
    9.9 KB · Views: 8
Beamer, You are Genius :dance:. It worked. Thank youuuuuuuuuuuuuuuuu
 
Beamer said:
I've added data validation drop-downs to Column F Just as a suggestion for you, might be quicker and save typo's.
Click to expand...

Beamer, I'm sorry but the cycle for the Semi-Annual has changed but I got confused on how to change it in the formula, it has been changed to 07.Jan and 07. July instead of 07.Feb and 07.Aug. Can you please help?
 
I don't blame you, I just had a look and totally forgot how I worked it out :D
But it appears that the third "IF" statement in the formula is where it decides its something other then "Annual" in column F.
So from that point on change any ",8,7" to ",7,7," and any ",2,7" to ",1,7"
The changed numbers relate to:- 8 = Aug, 7 = Jul, 2 = Feb, 1 = Jan
The "Date" function is in the format Year/Month/Day.

I hope this works for you.

Edit: I didn't change my formula and check the dates. I suggest checking the dates very close to the July date and if its out by a day you might need to change the 184 to 183...not sure
 
Last edited:
Beamer said:
I don't blame you, I just had a look and totally forgot how I worked it out :D
But it appears that the third "IF" statement in the formula is where it decides its something other then "Annual" in column F.
So from that point on change any ",8,7" to ",7,7," and any ",2,7" to ",1,7"
The changed numbers relate to:- 8 = Aug, 7 = Jul, 2 = Feb, 1 = Jan
The "Date" function is in the format Year/Month/Day.

I hope this works for you.

Edit: I didn't change my formula and check the dates. I suggest checking the dates very close to the July date and if its out by a day you might need to change the 184 to 183...not sure
Click to expand...



God! it didn't work out, specially when the start/end date is having the month of the bill cycle (Jan or July)
 
It seems to work ok for me, at least the same as the last one.
Fingers crossed :)
 

Attachments

  • Annual Bills.xlsx
    10.1 KB · Views: 4
You must log in or register to reply here.
Back
Top