Excel Formula to update itself automatically.
- Thread starter FrancisM
- Start date
- Joined
- Dec 13, 2017
- Messages
- 71
- Reaction score
- 19
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
- Dec 13, 2017
- Messages
- 71
- Reaction score
- 19
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...
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
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
- 71
- Reaction score
- 19
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
- Dec 13, 2017
- Messages
- 71
- Reaction score
- 19
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.