Solved

Excel Time Sheet

Posted on 2014-02-12
8
385 Views
Last Modified: 2014-02-12
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
Comment
Question by:karnac2020
  • 5
  • 3
8 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39855094
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 81

Expert Comment

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

Author Comment

by:karnac2020
ID: 39855099
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

by:karnac2020
ID: 39855101
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:byundt
ID: 39855172
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
 
LVL 81

Expert Comment

by:byundt
ID: 39855187
Workbook updated with formulas in column D linking the wage to what you put in cell D5.
GenericQ28363621.xlsm
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39855195
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
 

Author Closing Comment

by:karnac2020
ID: 39855209
Thanks Brad! Great solution.
Mark
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now