open folder with fiscal year period

[preciouslife73]

I am trying to open a folder that has a fiscal year period.

I have everything that is needed except for the period, for example October month is the 1st fiscal period and September is that last fiscal period (12th).

Here is how my folder path is created:

c:\\financial statement\FY2016\12 Sept 2016\09172016

I can dim the month and the year as as Format(Now, "mmm") and format(Now, "yyyy")

and I can dim the date as Format(Date, "mmddyyyy")

For the fiscal year "FY2016" I have figured out already.

But for the fiscal month period number "12", I cant seem to find in any thread that will convert this month as 12 instead of 9. I did try this:
Format( Now, "mm" + 3) and it gave me a 12 which is good but when October comes, it will change to 13 instead of 01..

Can you someone help with this?

[Kamboj]

=IF(month(now())<=9,format(Now,"mm"+3),format(Now,"mm"-9))

[preciouslife73]

The if wont work in vba, it requires an end statement.

=IF(month(now())<=9,format(Now,"mm"+3),format(Now,"mm"-9))

[LeoTaxi]

Maybe

If month(Date)<10 then mymonth = Format(Now, "mm" +3)
If month(Date)>9 then mymonth = Format(Now, "mm" -9)

Kind regards
Leo

[LeoTaxi]

The if wont work in vba, it requires an end statement.

not if all is wrtten in 1 row


Kind regards
Leo

[preciouslife73]

It won't accept both in the same row.
Or separate rows.

not if all is wrtten in 1 row


Kind regards
Leo

[LeoTaxi]

Both are working without End If

Sub test()
Range("B1") = IIf(Month(Range("A1")) < 10, Month(Range("A1")) + 3, Month(Range("A1")) - 9)
End Sub

Sub test2()
    If Month(Range("A1")) < 10 Then Range("B1") = Month(Range("A1")) + 3
    If Month(Range("A1")) > 9 Then Range("B1") = Month(Range("A1")) - 9
End Sub

Cheers
Leo

[preciouslife73]

Thanks, I will try again in the morning.

Both are working without End If

Sub test()
Range("B1") = IIf(Month(Range("A1")) < 10, Month(Range("A1")) + 3, Month(Range("A1")) - 9)
End Sub

Sub test2()
    If Month(Range("A1")) < 10 Then Range("B1") = Month(Range("A1")) + 3
    If Month(Range("A1")) > 9 Then Range("B1") = Month(Range("A1")) - 9
End Sub

Cheers
Leo

[preciouslife73]

Seems to be working. Is there anyway to format the month in all caps?
Here's what I got MTH = Format(Now, "MMM")
I am using theses to open a folder but doesn't seem to open. Maybe the month is not capitalized that is giving me a mismatch 13.

Thanks, I will try again in the morning.

[LeoTaxi]

This gives the result in format "mmm"

Sub test()
Range("B1") = IIf(Month(Range("A1")) < 10, Format("1/" & Month(Range("A1")) + 3 & "/" & Year(Range("A1")), "mmm"), Format("1/" & Month(Range("A1")) - 9 & "/" & Year(Range("A1")), "mmm"))
End Sub

Sub test2()
    If Month(Range("A1")) < 10 Then Range("B1") = Format("1/" & Month(Range("A1")) + 3 & "/" & Year(Range("A1")), "mmm")
    If Month(Range("A1")) > 9 Then Range("B1") = Format("1/" & Month(Range("A1")) - 9 & "/" & Year(Range("A1")), "mmm")
End Sub

Kind regards
Leo

[preciouslife73]

I don't use cell range.

This gives the result in format "mmm"

Sub test()
Range("B1") = IIf(Month(Range("A1")) < 10, Format("1/" & Month(Range("A1")) + 3 & "/" & Year(Range("A1")), "mmm"), Format("1/" & Month(Range("A1")) - 9 & "/" & Year(Range("A1")), "mmm"))
End Sub

Sub test2()
    If Month(Range("A1")) < 10 Then Range("B1") = Format("1/" & Month(Range("A1")) + 3 & "/" & Year(Range("A1")), "mmm")
    If Month(Range("A1")) > 9 Then Range("B1") = Format("1/" & Month(Range("A1")) - 9 & "/" & Year(Range("A1")), "mmm")
End Sub

Kind regards
Leo

[LeoTaxi]

If you use a string replace Range("B1") to name of your string

Cheers
Leo