Macro to complete date form
Closed
DazJ
Posts
4
Registration date
Tuesday February 24, 2015
Status
Member
Last seen
February 25, 2015
-
Feb 24, 2015 at 05:24 PM
cmarzahn Posts 35 Registration date Wednesday February 18, 2015 Status Member Last seen March 13, 2015 - Feb 25, 2015 at 06:44 PM
cmarzahn Posts 35 Registration date Wednesday February 18, 2015 Status Member Last seen March 13, 2015 - Feb 25, 2015 at 06:44 PM
Hello,
I've got a spreadsheet with the Month in the top left "A5:B7" merged cells, the numerical days of the week (only Mon-Fri) on the right "L9:AJ10".
At the moment I am manually changing the Month on each sheet (30+ in the workbook) and also changing the numerical days for Mon-Fri
I'm just wondering if there was a possibility to create a macro to pop up a message on a master file asking which Month/Year to create the spreadsheet for, this will then create a new spreadsheet using the template automatically changing the Month and date cells.
I've got a spreadsheet with the Month in the top left "A5:B7" merged cells, the numerical days of the week (only Mon-Fri) on the right "L9:AJ10".
At the moment I am manually changing the Month on each sheet (30+ in the workbook) and also changing the numerical days for Mon-Fri
I'm just wondering if there was a possibility to create a macro to pop up a message on a master file asking which Month/Year to create the spreadsheet for, this will then create a new spreadsheet using the template automatically changing the Month and date cells.
Related:
- Macro to complete date form
- Wpan full form - Guide
- Could not complete this operation. there was not enough memory available - Guide
- Utorrent shutdown when downloads complete - Guide
- Instagram appeal form for disabled account - Guide
- Facebook group disabled appeal form - Guide
1 response
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Feb 24, 2015 at 05:45 PM
Feb 24, 2015 at 05:45 PM
Don't really need a macro. I often put a "Driver" worksheet in workbooks I build. One of the drivers (or inputs) could be the start date for the range of dates on the other sheets.
Within the functions, consider using =now() in combo at the right times.
For example, I build 'em so the now() function updates files to the current year when opened.
To find the end of the month, for example, it might go like this:
=EOMONTH(NOW(),0)
To get the day of the month you could use:
=DAY(NOW())
To get the Weekday is a bit more involved.
=CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
Hope that helps!
Within the functions, consider using =now() in combo at the right times.
For example, I build 'em so the now() function updates files to the current year when opened.
To find the end of the month, for example, it might go like this:
=EOMONTH(NOW(),0)
To get the day of the month you could use:
=DAY(NOW())
To get the Weekday is a bit more involved.
=CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
Hope that helps!
Feb 24, 2015 at 06:03 PM
Feb 24, 2015 at 06:14 PM
Feb 24, 2015 at 10:23 PM
Look for the "drivers" sheet at the end of the worksheets.
Put the Start of the month in the yellow cell
It finds the day of the end of the month.
Fill in the dates of the first week of the month (Yellow) and the rest happens automatically. Saved a copy to the same place on Google Drive.
https://docs.google.com/spreadsheets/d/1i6KFwZHJLTD0_6uXvsygV1cC9vK5oh8o-rkZyclCOD4/edit?usp=sharing
Feb 25, 2015 at 05:17 AM
Yeah, everything is working well here, just need to add conditional formatting. If the days of the week = "0" or " " then grey the cells below out.
Feb 25, 2015 at 06:44 PM