Excel Formula to update itself automatically.


Joined
Dec 13, 2017
Messages
45
Reaction score
11
I have a formula that would update itself automatically. For several years it worked great now it is giving false dates
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
45
Reaction score
11
Please share the details of the formula.
I want to apologize to you and everyone else. I failed to provide the formula.

J7 has the current date, J8 has the 1st day of the fiscal year. K6 has the 1st day in October, K 7 has the last day. L6 has the 1st day of November, L7 has the last day. M6 has the 1st day of December, M7 has the last day. N6 has the 1at day of January, N7 has the last day. O6 has the 1st day of February, O7 has the last. P6 has the 1st day of March, P7 has the last. K9 has the 1st day of April, K10 has the last. L9 has the 1st day of May, L10 has the last day. M9 has the 1st day of June, M10 has the last. N9 has the 1st day of July, N10 has the last. O9 has the 1st day of August, O10 has the last. P9 has the 1st day of September, P10 has the last day of the month.

The current formula for the cells is this J7, =TODAY(), J8 static date - (currently 1st day of the fiscal year - 43739). (October) K6, =IF(P10>J7,J8,P10); K7,=EOMONTH(K6,0). (November) L6, =K7+1; L7, =EOMONTH(K6,1). (December) M6, =L7+1; M7, =EOMONTH(K6,2). (January) N6, =M7+1; N7, =EOMONTH(K6,3). February) N6, =N7+1; N7, =EOMONTH(K6,3). (March) O6, =O7+1; O7, =EOMONTH(K6,5). (April) K9, =P7+1; K10, =EOMONTH(K6,6). (May) L9, =K10+1; L10, =EOMONTH(K6,7). (June) M9, =L10+1; M10, =EOMONTH(K6,8). (July) N9, =M10+1; N10, =EOMONTH(K6,9). (August) O9, =N10+1 ; O10, =EOMONTH(K6,10). (September) P9, =O10+1; P10, =EOMONTH(K6,11).

Here is what is shown in the cells currently ( as of 10/7/2019). J7, 10/7/2019; J8, 10/1/2019; K6, 10/1/2019; K7, 10/31/2019; L6, 11/1/2019; L7, 11/30/2019; M6, 12/1/2019; M7, 12/31/2019; N6, 1/1/2020; N7, 1/31/2020; O6, 2/1/2020; O7, 2/29/2020; P6, 3/1/2020; P7, 3/31/2020; K9, 4/1/2020; K10, 4/30/2020; L9, 5/1/2020; L10, 5/31/2020; M9, 6/1/2020; M10, 6/30/2020; N9, 7/1/2020; N10, 7/31/2020; O9, 8/1/2020; O10, 8/31/2020; P9, 9/1/2020; P10, 9/30/2020.

I am trying to find a formula for J8 (static date). Currently the user has to manually change the date every year. I am trying to avoid that.
 
Joined
Feb 21, 2018
Messages
160
Reaction score
62
Select K6 and type 10/1/2019
Select K7 and type =DATE(YEAR(+K6+32),MONTH(K6+32),1)-1
Select L6 and type =DATE(YEAR(+K6+32),MONTH(K6+32),1)
Copy L6:L7 formula right wards on 12 columns
Move Q6:V7 to K10
 
Joined
Dec 13, 2017
Messages
45
Reaction score
11
Select K6 and type 10/1/2019
Select K7 and type =DATE(YEAR(+K6+32),MONTH(K6+32),1)-1
Select L6 and type =DATE(YEAR(+K6+32),MONTH(K6+32),1)
Copy L6:L7 formula right wards on 12 columns
Move Q6:V7 to K10
This looks similar to the current formula, except different formula. It appears that you still have to use a static date. Also I don't understand "Move Q6:V7 to K10" . What is in the range? Is there some way of getting rid of the static date?
 
Joined
Feb 21, 2018
Messages
160
Reaction score
62
This looks similar to the current formula, except different formula. It appears that you still have to use a static date. Also I don't understand "Move Q6:V7 to K10" . What is in the range? Is there some way of getting rid of the static date?
Yes... Let me get back to my laptop... I am away.... Try writing a change or, Beforedouble click event.... That will advance the static cell to 1st day of previous /next year...
 
  • Like
Reactions: Ian
Ad

Advertisements

Joined
Feb 21, 2018
Messages
160
Reaction score
62
Copy the following in your worksheet code area....then double click on J8 to jump to the next year..
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
tgt = "$J$8"
If Target.Address <> UCase(tgt) Then Exit Sub

Target.Formula = "=date(year(datevalue(""" & Target & """))+1,10,1)"
Range("K6") = Target
Target.Offset(1, 0).Select
End Sub
 
Joined
Dec 13, 2017
Messages
45
Reaction score
11
Copy the following in your worksheet code area....then double click on J8 to jump to the next year..
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
tgt = "$J$8"
If Target.Address <> UCase(tgt) Then Exit Sub

Target.Formula = "=date(year(datevalue(""" & Target & """))+1,10,1)"
Range("K6") = Target
Target.Offset(1, 0).Select
End Sub
It works. However it requires the user to double click on cell J8. Would it be possible avoid any user interaction, & let the system change that date?
 
Joined
Feb 21, 2018
Messages
160
Reaction score
62
OK remove all vba code.
Write this formula in the cell j8.

=IF(J7<=P11,DATE(Year(P10)-1,10,1),P11+1)

Write this formula in the cell K6
=+J8
 
Joined
Dec 13, 2017
Messages
45
Reaction score
11
OK remove all vba code.
Write this formula in the cell j8.

=IF(J7<=P11,DATE(Year(P10)-1,10,1),P11+1)

Write this formula in the cell K6
=+J8
I cant believe how simple the formula is. You are a genius. The only thing I had to do was move P10 to P9 & P11 to P10. I have struggling for the past month to do what you did. Thank you.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top