Excel Formula to update itself automatically.

[FrancisM]

I have a formula that would update itself automatically. For several years it worked great now it is giving false dates

 

[AmjiBhai]

Please share the details of the formula.

 

[FrancisM]

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.

 

[AmjiBhai]

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

 

[FrancisM]

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?

 

[AmjiBhai]

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...

 

[AmjiBhai]

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

 

[FrancisM]

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?

 

[AmjiBhai]

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

 

[FrancisM]

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.

 

[AmjiBhai]

Thanks for your kind appreciation!