Solved

# Excel Time Sheet

Posted on 2014-02-12
384 Views
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.

Mark
Generic.xlsx
0
Question by:karnac2020
• 5
• 3

LVL 80

Expert Comment

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

LVL 80

Expert Comment

And for cell A5, I used this formula:
=--MID(B1,13,30)
0

Author Comment

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

Author Comment

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?

Mark
0

LVL 80

Expert Comment

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.

GenericQ28363621.xlsm
0

LVL 80

Expert Comment

Workbook updated with formulas in column D linking the wage to what you put in cell D5.
GenericQ28363621.xlsm
0

LVL 80

Accepted Solution

byundt earned 500 total points
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.

0

Author Closing Comment

Mark
0

## Featured Post

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…