Recurring date formula
Solved/Closed
MRafik
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
-
Aug 10, 2013 at 09:39 AM
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013 - Aug 15, 2013 at 02:11 PM
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013 - Aug 15, 2013 at 02:11 PM
Related:
- Recurring date formula
- Date formula in excel dd/mm/yyyy - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Credit summation formula - Guide
9 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 12, 2013 at 10:49 AM
Aug 12, 2013 at 10:49 AM
Hi MRafik,
The problems you encounter are exactly the ones where you use EDATE instead of just adding the number of days.
So change that and you should be fine.
And next time use cell references that match the ones in your formula for faster understanding.
Best regards,
Trowa
The problems you encounter are exactly the ones where you use EDATE instead of just adding the number of days.
So change that and you should be fine.
And next time use cell references that match the ones in your formula for faster understanding.
Best regards,
Trowa
MRafik
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
Aug 12, 2013 at 11:30 AM
Aug 12, 2013 at 11:30 AM
Hi Trowa
Thanks for the reply, but I still am lost as I dont know
wether to put in 30 or 31 for the number of days as i
have copied the cells from B8 onwards to B32.
Regards
Thanks for the reply, but I still am lost as I dont know
wether to put in 30 or 31 for the number of days as i
have copied the cells from B8 onwards to B32.
Regards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 12, 2013 at 11:37 AM
Aug 12, 2013 at 11:37 AM
Well, you could use the function MONTH to determine the month of the start date and go from there.
MRafik
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
Aug 12, 2013 at 12:49 PM
Aug 12, 2013 at 12:49 PM
Hi Trowa
Once again thanks for your reply but I am still lost as am very new
to all this
I changed the EDATE to MONTH but that does not work
Regards
Once again thanks for your reply but I am still lost as am very new
to all this
I changed the EDATE to MONTH but that does not work
Regards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 13, 2013 at 10:36 AM
Aug 13, 2013 at 10:36 AM
Hi MRafik,
Then I need to know when you want to add x numbers of days.
Let's say your start date is 31-1-2013.
And you choose option 3; Monthly.
Your formula would show as result 28-2-2013.
What is the result you would like to see?
1-3-2013 (+29), 2-3-2013 (+30) or 3-3-2013 (+31).
And what result would you like to see when start date is 28-2-2013?
And what if start date is 31-3-2013?
Don't you always want to add 31 when option 3 is chosen?
As you can see it is not very clear to me what result you are after.
So please provide some results your formula should give you with certain start dates.
Best regards,
Trowa
Then I need to know when you want to add x numbers of days.
Let's say your start date is 31-1-2013.
And you choose option 3; Monthly.
Your formula would show as result 28-2-2013.
What is the result you would like to see?
1-3-2013 (+29), 2-3-2013 (+30) or 3-3-2013 (+31).
And what result would you like to see when start date is 28-2-2013?
And what if start date is 31-3-2013?
Don't you always want to add 31 when option 3 is chosen?
As you can see it is not very clear to me what result you are after.
So please provide some results your formula should give you with certain start dates.
Best regards,
Trowa
Didn't find the answer you are looking for?
Ask a question
MRafik
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
Aug 13, 2013 at 11:37 AM
Aug 13, 2013 at 11:37 AM
Hi Trowa
Thanks for the reply
My work sheet is for rents due so if the rent due is on the 1st
of each month then the cell B8 will be 01/01/2013 and B9 will
be 01/02/2013 and so on. So if start date is 29/01/2013 then the next cell
should be 28/02/2013(except when it is a leap year) and the next
should be 29/03/2013.
If the start date is 30/01/2013 then the next should be 28/02/2013
(except when it is a leap year) and the next should be 30/03/2013
etc.
If start date is 31/01/2013 then it should be 28/02/2013(except when it is a leap year)and the next one should be 31/03/2013 and the next 30/04/2013 ie the last
day of each month.
I tried the following IF($AA$2=3,DATE(YEAR(B8),MONTH(B8)+1,DAY(B8) for
option 3 but it does not work.
Please note my options are in a combo box .
Rgards
Thanks for the reply
My work sheet is for rents due so if the rent due is on the 1st
of each month then the cell B8 will be 01/01/2013 and B9 will
be 01/02/2013 and so on. So if start date is 29/01/2013 then the next cell
should be 28/02/2013(except when it is a leap year) and the next
should be 29/03/2013.
If the start date is 30/01/2013 then the next should be 28/02/2013
(except when it is a leap year) and the next should be 30/03/2013
etc.
If start date is 31/01/2013 then it should be 28/02/2013(except when it is a leap year)and the next one should be 31/03/2013 and the next 30/04/2013 ie the last
day of each month.
I tried the following IF($AA$2=3,DATE(YEAR(B8),MONTH(B8)+1,DAY(B8) for
option 3 but it does not work.
Please note my options are in a combo box .
Rgards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 13, 2013 at 12:06 PM
Aug 13, 2013 at 12:06 PM
Hi MRafik,
Let's take a look at the part of the formula for option 3 in cell B9.
IF($AA$2=3,EDATE(B8,1)
Let's change that into:
IF($AA$2=3,EDATE(B$8,CELL("row",B9)-8)
Best regards,
Trowa
Let's take a look at the part of the formula for option 3 in cell B9.
IF($AA$2=3,EDATE(B8,1)
Let's change that into:
IF($AA$2=3,EDATE(B$8,CELL("row",B9)-8)
Best regards,
Trowa
MRafik
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
Aug 13, 2013 at 12:27 PM
Aug 13, 2013 at 12:27 PM
Hi Trowa
Thank you so much for all your help
It does work
Will you please explain the logic behind the formula as
i still have to try and sort the quarterly , six monthly and annually.
ie options 4,6 and 7.
Once again thank you
Regards
MRafik
Thank you so much for all your help
It does work
Will you please explain the logic behind the formula as
i still have to try and sort the quarterly , six monthly and annually.
ie options 4,6 and 7.
Once again thank you
Regards
MRafik
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 15, 2013 at 10:54 AM
Aug 15, 2013 at 10:54 AM
Hi MRafik,
The problem was that your reference to the start date was relative.
Meaning that:
1 month + 31-1-2013 (B8) = 28-2-2013 (B9)
1 month + 28-2-2013 (B9) = 28-3-2013 (B10)
Making the reference absolute was the first step, but then we need something to add months.
Otherwise we would get:
1 month + 31-1-2013 (B8) = 28-2-2013 (B9)
1 month + 31-1-2013 (B8) = 28-3-2013 (B10)
[ CELL("row",B9)-8 ] is just used as a counter.
[ CELL("row",B9) ] will result 9 as is the row number of B9.
Deduct 8 from 9 = 1 or 1 month.
For the next row the result will be 10 as is the row number of B10.
Deduct 8 from 10 = 2 or 2 months.
and so on...
So for monthly the counter would need to add 1.
But for Quarterly the counter would need to add 3.
So think about how you can get 3, 6, 9 ...
Solution:
Use row of B1 = 1, times 3 = 3
Next cell would be B2 = 2, times 3 = 6
Thus part of formula would be:
IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)
Now that I think about it a second time this basically solves it all, right?
Monthly: IF($AA$2=3,EDATE(B$8,CELL("row",B1)
Quarterly: IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)
Half Yearly: IF($AA$2=6,EDATE(B$8,CELL("row",B1)*6)
Annually: IF($AA$2=7,EDATE(B$8,CELL("row",B1)*12)
Best regards,
Trowa
The problem was that your reference to the start date was relative.
Meaning that:
1 month + 31-1-2013 (B8) = 28-2-2013 (B9)
1 month + 28-2-2013 (B9) = 28-3-2013 (B10)
Making the reference absolute was the first step, but then we need something to add months.
Otherwise we would get:
1 month + 31-1-2013 (B8) = 28-2-2013 (B9)
1 month + 31-1-2013 (B8) = 28-3-2013 (B10)
[ CELL("row",B9)-8 ] is just used as a counter.
[ CELL("row",B9) ] will result 9 as is the row number of B9.
Deduct 8 from 9 = 1 or 1 month.
For the next row the result will be 10 as is the row number of B10.
Deduct 8 from 10 = 2 or 2 months.
and so on...
So for monthly the counter would need to add 1.
But for Quarterly the counter would need to add 3.
So think about how you can get 3, 6, 9 ...
Solution:
Use row of B1 = 1, times 3 = 3
Next cell would be B2 = 2, times 3 = 6
Thus part of formula would be:
IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)
Now that I think about it a second time this basically solves it all, right?
Monthly: IF($AA$2=3,EDATE(B$8,CELL("row",B1)
Quarterly: IF($AA$2=4,EDATE(B$8,CELL("row",B1)*3)
Half Yearly: IF($AA$2=6,EDATE(B$8,CELL("row",B1)*6)
Annually: IF($AA$2=7,EDATE(B$8,CELL("row",B1)*12)
Best regards,
Trowa
MRafik
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
Aug 15, 2013 at 02:11 PM
Aug 15, 2013 at 02:11 PM
THANK YOU VERY MUCH FOR ALL YOUR HELP
Regards
Mrafik
Regards
Mrafik
