+ Reply to Thread
Results 1 to 12 of 12

open folder with fiscal year period

  1. #1
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119

    open folder with fiscal year period

    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?

  2. #2
    Valued Forum Contributor Kamboj's Avatar
    Join Date
    09-25-2014
    Location
    India
    MS-Off Ver
    2003 - 2010
    Posts
    430

    Re: open folder with fiscal year period

    Please Login or Register  to view this content.
    Kamboj
    _________________________________________________________________________________
    Mark the thread as SOLVED if my answer satisfy you.

  3. #3
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: open folder with fiscal year period

    Maybe

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

    Kind regards
    Leo

  4. #4
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119
    The if wont work in vba, it requires an end statement.

    Quote Originally Posted by Kamboj View Post
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: open folder with fiscal year period

    The if wont work in vba, it requires an end statement.
    not if all is wrtten in 1 row


    Kind regards
    Leo

  6. #6
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119
    It won't accept both in the same row.
    Or separate rows.

    Quote Originally Posted by LeoTaxi View Post
    not if all is wrtten in 1 row


    Kind regards
    Leo

  7. #7
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: open folder with fiscal year period

    Both are working without End If

    Please Login or Register  to view this content.
    Cheers
    Leo

  8. #8
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119
    Thanks, I will try again in the morning.


    Quote Originally Posted by LeoTaxi View Post
    Both are working without End If

    Please Login or Register  to view this content.
    Cheers
    Leo

  9. #9
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119
    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.
    Quote Originally Posted by preciouslife73 View Post
    Thanks, I will try again in the morning.
    Last edited by preciouslife73; 09-20-2016 at 12:03 PM.

  10. #10
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: open folder with fiscal year period

    This gives the result in format "mmm"

    Please Login or Register  to view this content.
    Kind regards
    Leo

  11. #11
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119
    I don't use cell range.

    Quote Originally Posted by LeoTaxi View Post
    This gives the result in format "mmm"

    Please Login or Register  to view this content.
    Kind regards
    Leo

  12. #12
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: open folder with fiscal year period

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

    Cheers
    Leo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 07-10-2015, 10:30 PM
  2. Transpose Quarters from Calendar Year to Fiscal Year
    By jodiander in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-25-2013, 01:00 PM
  3. how to convert date to fiscal year if fiscal year start at 16th or 17th of July
    By sushil shakya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2013, 03:32 AM
  4. [SOLVED] Create Fiscal Year - Lookup Date in Fiscal Month Date Range and Return EOM Date
    By gbriscoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 03:29 PM
  5. Determine if date is before end of fiscal year of previous year
    By ccarver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2010, 11:53 AM
  6. Look Up Fiscal Year and Period from Table
    By bberks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2010, 09:58 AM
  7. To automatically give fiscal period/fiscal year
    By Turnipboy in forum Excel General
    Replies: 7
    Last Post: 01-19-2006, 05:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1