Solved

Excel Time Sheet

Posted on 2014-02-12
8
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 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