Tried rebooting, but to no avail. This code to establish dates worked perfectly over a year....
Code:
Public show_Day As Variant
Public show_Day_short As Variant
Public show_Month As Variant
Public show_Month_short As Variant
Public show_Month_mid As Variant
Public show_Month_long As Variant
Public show_Year As Variant
Public show_year_short As Variant
Public show_Year_long As Variant
Public fiscal_month As Variant
Public fiscal_month_2 As Variant
Public fiscal_year As Variant
Public p_show_Day As Variant
Public p_show_Day_short As Variant
Public p_show_Month As Variant
Public p_show_Month_short As Variant
Public p_show_Month_mid As Variant
Public p_show_Month_long As Variant
Public p_show_Year As Variant
Public p_show_year_short As Variant
Public p_show_Year_long As Variant
Public p_fiscal_month As Variant
Public p_fiscal_month_2 As Variant
Public p_fiscal_year As Variant
Public f_show_Day As Variant
Public f_show_Day_short As Variant
Public f_show_Month As Variant
Public f_show_month_short As Variant
Public f_show_Month_mid As Variant
Public f_show_month_long As Variant
Public f_show_Year As Variant
Public f_show_year_short As Variant
Public f_show_Year_long As Variant
Public f_fiscal_month As Variant
Public f_fiscal_month_2 As Variant
Public f_fiscal_year As Variant
Public Sub start_dates()
Range("report_date") = Date
d0 = Range("eom").Value
show_Day_short = format(d0, "D")
show_Day = format(d0, "DD")
show_Month_short = format(d0, "M")
show_Month = format(d0, "MM")
show_Month_mid = format(d0, "MMM")
show_Month_long = format(d0, "MMMM")
show_year_short = format(d0, "YY")
show_Year = format(d0, "YYYY")
d1 = Range("fom").Value
f_show_Day_short = format(d1, "D")
f_show_Day = format(d1, "DD")
f_show_month_short = format(d1, "M")
f_show_Month = format(d1, "MM")
f_show_Month_mid = format(d1, "MMM")
f_show_month_long = format(d1, "MMMM")
f_show_year_short = format(d1, "YY")
f_show_Year = format(d1, "YYYY")
d2 = Range("p_eom").Value
p_show_Day_short = format(d2, "D")
p_show_Day = format(d2, "DD")
p_show_Month_short = format(d2, "M")
p_show_Month = format(d2, "MM")
p_show_Month_mid = format(d2, "MMM")
p_show_Month_long = format(d2, "MMMM")
p_show_year_short = format(d2, "YY")
p_show_Year = format(d2, "YYYY")
'fiscal month
fiscal_month = Choose(show_Month_short, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2)
If Len(fiscal_month) = 1 Then
fiscal_month = 0 & fiscal_month
End If
'fiscal month without 0 in front
fiscal_month_2 = Choose(show_Month_short, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2)
'fiscal year
fiscal_year = Choose(show_Month_short, show_Year, show_Year, show_Year, show_Year, show_Year, show_Year, show_Year, show_Year, show_Year, show_Year, show_Year + 1, show_Year + 1)
'fiscal month
p_fiscal_month = Choose(p_show_Month_short, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2)
If Len(p_fiscal_month) = 1 Then
p_fiscal_month = 0 & p_fiscal_month
End If
'fiscal month without 0 in front
p_fiscal_month_2 = Choose(p_show_Month_short, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2)
'fiscal year
p_fiscal_year = Choose(p_show_Month_short, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year, p_show_Year + 1, p_show_Year + 1)
'previous fiscal month
f_fiscal_month = Choose(f_show_month_short, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2)
If Len(p_fiscal_month) = 1 Then
f_fiscal_month = 0 & f_fiscal_month
End If
' previous fiscal month without 0 in front
f_fiscal_month_2 = Choose(p_show_Month_short, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2)
'previous fiscal year
f_fiscal_year = Choose(f_show_month_short, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year, f_show_Year + 1, f_show_Year + 1)
End Sub
