How to change formula to see only selected month RRS feed

  • Question

  • Dear all,

    I need to handle a monthly time tracking in excell for our project acvtivities.

    For that I have used an free sample template found around.
    When you open the excel file template you are able to set the starting date and it will automatically displays days as see below picture


    As you can see above it show the start cell as 6 of january but it also display the february month.

    I could not find from the template itself how to display only the day entry for the month ( jan in our sample)

    Any idea ?

    You can get the template I was using from following url

    Monthly excell template

    Thanks for help


    Wednesday, January 8, 2020 8:49 AM

All replies

  • It would be possible to create formulas to leave the columns past the end of the month blank. However, the totals at the right would then have blank columns after the data.

    The template you refer to is copyrighted and making changes to it might infringe copyright but not being a copyright lawyer I am not sure about this.

    Are you interested in a VBA code (Macro) solution? With an entirely different method to the template you should not be infringing any copyright.

    With VBA it is possible to have a Main Menu sheet and enter a start date and the code will create a new sheet and complete the remainder of the month column headers and also place the total calculations in the next column to the right of the last day of the month.

    Could have a template sheet which would have the required company name and any other info that will be common to all sheets and it will be copied to a new sheet as the start of the process.

    It is also possible to have the VBA code in a separate workbook so that you can either add a worksheet to an existing xlsx workbook or even create an entirely new workbook for each month and if the data needs to be distributed then the code is not distributed with it.

    I can assist by providing the code so let me know if you are interested and also respond to the following questions.

    1. Do you want to have the code in a separate workbook?
    2. Do you want to add a sheet to existing workbook?
    3. Or do you want an entirely new workbook each month.

    Regards, OssieMac

    Sunday, January 12, 2020 6:07 AM
  • I propose that you use a 4-5-4 or equivalent Fiscal Calendar
    that always has 4 or 5 whole weeks each month (aka Period).

    Sunday, January 12, 2020 7:39 PM