Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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
0
karnac2020
Asked:
karnac2020
  • 5
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
byundtCommented:
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
 
karnac2020Author Commented:
Thanks Brad! Great solution.
Mark
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now