?
Solved

Excel Time Sheet

Posted on 2014-02-12
8
Medium Priority
?
401 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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
 
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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

589 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