Excel Time Sheet

I am using Excel 2011 on a Mac to create a monthly time sheet. I have attached a generic sheet that I use as a model to create a different time sheet for each month.

Manually entering the dates each month for Feb, March, etc. is laborious. I would like to create a template or model sheet where I could:
1) have a variable for the Hourly Rate
2) Have the "blocks" for each day of the month be prefaced by the date, e.g., 02/11/14, 03/31/14, etc. for each month from January to December.

Is there a way to create a sheet (maybe it uses macros which I am not familiar with) to start at the top by entering the Month, and then have the 28 to 31 "blocks" representing each day automatically have their date (e.g., 3/13/14) filled in?

I would like to avoid manually having to fill in the date for each daily block.

Thanks in advance,

Mark
Generic.xlsx
karnac2020Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Mark,
To make a sheet for a new month:
1) Control-click the sheet tab and choose "Move or Copy..."
2) In the resulting dialog, check the box for "Create a copy"
3) Change the name on the sheet tab to suit, then change the caption in cell B1 to the new month. This caption must follow the pattern "Invoice for February 2014" as the date formulas are looking for the name of a month and a year starting with the 13th character.

Brad
0
 
byundtCommented:
I put the following formula in cell A6 and then copied it down. It will return the dates for you automatically, until the end of the month.
=IF(AND(C5="",OR(C6<>"",MAX(A$5:A5)<EOMONTH(A$5,0))),MAX(A$5:A5)+1,"")
0
 
byundtCommented:
And for cell A5, I used this formula:
=--MID(B1,13,30)
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
karnac2020Author Commented:
Hi byundt,

Did you include the file as an attachment? I don't see it here.

It would be helpful because I am a total novice with Excel.

Thanks
mark
0
 
karnac2020Author Commented:
Byundt,

I put those two formulas into the cells, but I don't know how to use the sheet to generate new sheet for each month. Also did you make the hourly rate a variable that I can just plug in?

Thanks in advance,

Mark
0
 
byundtCommented:
I changed the formula in cell A6 to:
=IF(AND(C5="",C6<>"",COUNTIF(B$5:B5,"Daily Total")<DAY(EOMONTH(A$5,0))),MAX(A$5:A5)+1,"")

I used the following Conditional formatting formula for cells A5:E198 to hide the borders and change the font color to white.
=COUNTIF($B$4:$B4,"Daily Total")>=DAY(EOMONTH($A$5,0))

I couldn't figure out how to get the bottom border right on the last Daily Total row on months with less than 31 days.

Brad
GenericQ28363621.xlsm
0
 
byundtCommented:
Workbook updated with formulas in column D linking the wage to what you put in cell D5.
GenericQ28363621.xlsm
0
 
karnac2020Author Commented:
Thanks Brad! Great solution.
Mark
0
All Courses

From novice to tech pro — start learning today.