We help IT Professionals succeed at work.

How to remove error prone manual operations from Time and Activity spreadsheet?

107 Views
Last Modified: 2020-07-23
I have a spreadsheet that tracks my activities and time. I saw some templates online, but I didn't find what I was looking for. Attached is my current Excel spreadsheet. It almost works (see the divide by 0), but I have to enter manual data per PayPeriod. I was hoping that the error-prone manual steps could be eliminated.

There are two payperiods per month:
- day 1 to day 15 inclusive
- day 16 to last day of month (variable)

# hours per PayPeriod must be 8*NumberWorkDays(M-F) per PayPeriod

As I enter my hours/day, the "Remaining Hrs" is reduced, and the Remaining Average Hours/Day is adjusted.

In the attached spreadsheet, I have to make manual entries for the "Days in PayPeriod" (H); the # weekend days in PayPeriod (G); the sum range since it can vary in each PayPeriod (E, G). Could you show me how to reduce the number of these manual entries?.

I have colored the first and last day of each PayPeriod as well as weekend days. I am not attached to any particular colors. This is my own personal form, so it does not have to conform to any rules other than being able to track my time and enter my activity. I think it would be useful for me to see the delineations of the PayPeriod and the weekends.
TimeRecords.xlsx
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
see the divide by 0
Where?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
There's a lot of work needed and I'm happy to help but can we take it one or two steps at a time? If so what should I do first?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
As a start, this workbook has a formula in cells H16 and H32 that calculates workdays excluding the holidays listed on the 'Holidays' sheet. The list is dynamic.

And I see the DIV error but I don't understand your "This entry causes G47 problem (5 - 5 is 0); G47 should be 9 with 1 day left" comment. Where does "5" come from and what does "9 with 1 day left" mean? What result do you want to see?

I can probably add a "New Pay Period" button which would add a new pay period to the end of the data on Sheet1. So for example if everything below row 16 did not exist, clicking the button would produce rows 17 to 32.
29186184.xlsm
CERTIFIED EXPERT

Author

Commented:
Thank you Martin. :)

>> one or two steps at a time - good idea. If so what should I do first?
  • Let's make "Holidays" the last step. I can handle the one holiday between now and September.
  • What are the other options?
  • Since dividing by 0 is not so good, maybe that should be high on the list.

>> Where does "5" come from and what does "9 with 1 day left" mean? What result do you want to see?

In the PayPeriod from Aug-1 to Aug-15, there are:
- 10 workdays (M-F): I enter manually the number 10 in H47.
- The total number hours allowed to work is 10*8 = 88 = 8 * H47

This entry causes G47 problem (5 - 5 is 0); G47 should be 9 with 1 day left
In the PayPeriod from Aug-1 to Aug-15, there are:
- 5 weekend days (I count this manually.)
  -- I insert the number of weekend days (5 in this case) in this formula:
      =F47/(COUNTBLANK(D33:D47) - 5)
      That is, the "average remaining hours/day" (G47) is "remaining hours"(F47) divided by the number of workdays left. In the example, there is one workday left (D46 is blank).
     
So, the correct number for the "average remaining hours/day" is:
9 hours/1 day = 9 hours/day. So, 9 should appear in G47.
This tells me that I better work 9 hours on Friday Aug-15 to get to the 80 hour mark.
Notice that if you remove one of the hours worked in column D, then G47 does not divide by 0.

If I never would enter hours on the weekend, I think the formula also works.

>> a "New Pay Period" button which would add a new pay period to the end of the data on Sheet1
With the current setup, I can plan in the future with Plan notes in the Notes column. Would this button allow me to go a couple months to mark down, for example, vacation, mandatory training deadlines, etc.?

---------
Columns A, B, C, D probably should remain as-is. Colors can change or be replaced with something else if that makes it easier (but I think to avoid confusion, I should be able to see the PayPeriod demarcations).
Columns E, F, G, H occur once per PayPeriod. If you find it easier to move them around, that probably works.
---------

I see you included a 29186184.xlsm file. What should I be looking at in it?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Let's make "Holidays" the last step. I can handle the one holiday between now and September.
I've actually already done that. See cells H16 and H32 in my workbook and also the 'Holidays' worksheet.

Would this button allow me to go a couple months to mark down...
Sure, how may pay periods at a time would you need?

Colors can change or be replaced with something else if that makes it easier...
I think that if there were a blank row between pay periods it would "read" better.
CERTIFIED EXPERT

Author

Commented:
>> See cells H16 and H32 in my workbook and also the 'Holidays' worksheet.
Oh, cool.

>> pay periods at a time would you need?
I guess 6 would do.

>> a blank row between pay periods it would "read" better.
Yes, especially if it were filled in so it looked different from the PayPeriod rows.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please verify that the result in 'F' should be the remaining hours divided by the number of rows where column column 'D' is blank and is not a weekend or a holiday. If that is true then I don't think 9.2 is correct for the first pay period. It seems to me that there are 6 days that meet that criteria: 9, 10, 11, 14, 15, and 16 so shouldn't the result be 46/6 for a rounded result of 7.7?
CERTIFIED EXPERT

Author

Commented:
6 days left is correct.
7.7 hours is correct.
CERTIFIED EXPERT

Author

Commented:
I created a sheet 2 as you did, and I copied your function to my script, and got #NAME
=NETWORKDAYS(A111,A126,Holidays)

Open in new window

I got this to work, but I do not know how you got your code to work.
=NETWORKDAYS(A111,A126,Holidays!A$2:A$3)

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry about that and it was poor design on my part because the Holidays in my formula is a Named Range on a worksheet named Holidays. It works but I shouldn't have done that because it's confusing. From any worksheet, select Formulas->Name Manager
Name Managerand then click on the Refers To formula. When you do that the values that make up the Named Range will be selected no matter how many there are.

Refers To
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
By the way, this is what I'm working on. Is it acceptable?
2020-06-23_08-05-24.png
CERTIFIED EXPERT

Author

Commented:
Hi Martin,

Re: Holidays - np, and I'll look at your post hopefully tonight. At least I got some of it right on my own. :)

The format looks cool. Just wondering about something I've been meaning to do. Could E, F, G, H could be packed into E? Maybe a label either in F, or just above the E entry. If this is not easy, then your layout is still perfect.

Thanks again.
-Paul
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Click the Add Pay Periods button in this workbook. The 'Pay Periods' sheet is set up with no data and in that situation it will ask you for the starting date, otherwise it will calculate it from the previous pay period. Two new sheets in the workbook are 'Sheet1 Orig" which is a copy of your Sheet1, and 'Template' which is used to make the formatting of the pay periods easier.
29186184a.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
We cross-posted.

I'm not certain what "Could E, F, G, H could be packed into E" means but if you want to somehow put all the formula results from those cells into E then that's not possible.
CERTIFIED EXPERT

Author

Commented:
For example, in an earlier version, I had H16 in E15. I was thinking that F16, G16, and H16, could fit in E15,14,13 as an example.

I clicked on the Add Pay Periods button - did not see a popup. I got: Compiler error: Variable not defined. I hit OK and strMonth is higlighted  
 in the line strMonth = Month(strDate).
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
At the top of AddPayPeriond you'll see this

Sub AddPayPeriond()

Dim strPP As String
Dim intPP As Integer
Dim strDate As String
Dim lngLastRow As Long
Dim lngStartRow As Long
Dim lngRow As Long
Dim wsT As Worksheet
Dim wsPP As Worksheet
Dim strEndDate As Date
Dim lngNextRow As Long
Dim srrMonth As String
Dim strYear As String
Dim intRequested As Integer

Open in new window

Change line 13 to

Dim strMonth As String

I was thinking that F16, G16, and H16, could fit in E15,14,13 as an example
I'm not sure why you want to do that, but sure it could be done. Let's see if the current workbook works for you after you make the above change.
CERTIFIED EXPERT

Author

Commented:
The only reason I was considering putting those items in one column was to avoid having to scroll to the right to see the data. The width of the spreadsheet as of now is about 25 to 30 in wide.

I'll make the change to line 13 when I turn the computer on tomorrow.
Thanks for your help.
CERTIFIED EXPERT

Author

Commented:
My brief test looked pretty good. I'll do some more just to be sure. Thanks.

I have an existing spreadsheet of similar format. Can I add this button to the existing spreadsheet, or should I copy the contents from Feb 1 to present to your spreadsheet and just start adding PayPeriods?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It depends on how similar it is. If when you are done testing you are happy with the result you could close this question and in a new one I could convert your existing data (including correcting the formula in column G) and I could also show what is now in columns E through H in a new row at the bottom of each pay period.
CERTIFIED EXPERT

Author

Commented:
I put in some hours and got an error. I have attached the file with the error. Did I enter the hours incorrectly?
29186184a_00_Error.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I neglected to test the situation where there were no blank hours in the workdays of a pay period.
29186184b.xlsm
CERTIFIED EXPERT

Author

Commented:
Martin,

I like your format. I think the formulas are working. Thanks for this spreadsheet.

Here's the strange part. I was entering times, and wasn't paying exact attention to the summary line. I don't recall what I did but I needed only 2-3 more days of time to enter for the Pay Period. There was some # error in G. So, I deleted the last time entry (and the # changed to a better value, and re-entered the entry I just deleted. The G field updated correctly. Strange, but at least easy to fix.

Here's the stranger part (or maybe less strange depending on your point of view). I finished entering the times for a time period. All was good. G showed 0, as expected. I then went searching for my Holidays that I had entered in some version. (I couldn't find it - must have delete that file.) When I went back to the file I had just edited, the G column was #Value. But weirder still was that all the G values on the Summary lines for the entire sheet were #Value. Strange. I deleted a time entry, and all the #Value in the sheet reverted to good numbers. Not sure how future PayPeriods could be affected when all I did was open up other related Excel documents.

What do you think about these strange, but workable, # messages?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I could not reproduce those problems, but the most likely culprit was the Application.Volatile statement I had (and now commented out) in my Rahd UDF in the attached workbook. I don't remember why I thought I needed it but some modest testing shows that the function still works without it. If you are not familiar with Application.Volatile, here is what Microsoft has to say about it:
[It] Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change.
29186184c.xlsm
CERTIFIED EXPERT

Author

Commented:
Application.Volatile - what is the use of this?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I (actually Microsoft) explained that at the end of my previous post, but I've attached a demo to illustrate what it does. In the attached workbook you'll find this UDF.

Function AddBelow(cell As Range)
'Application.Volatile
AddBelow = cell.Value + cell.Offset(1, 0).Value
End Function

Open in new window


And in in cell C3 I have the formula =AddBelow(A1) and in this discussion I'll refer to A1 as the "target" cell.
Note that for now, line 2 is commented out. What the function does is to add the value below the target to the target itself. In other words it adds 10 to 7.

If you change the value in A1 the total in C3 will change. If however you change the value in A2 the total will not change and that is because the function is only aware of the range referred to in the formula (A1) and will only recalculate if that range changes..

If you now uncomment line 2 and then change the value in A2, the value in C3 will change because the function will recalculate when any cell is changed.
VolatileDemo.xlsm
CERTIFIED EXPERT

Author

Commented:
Thanks.

>> If you now uncomment line 2
How do I do this?
View --> Macros; Macros in: "All Open Workbooks" --> List is empty.

Also, please see #values (G99 and after) in attached.
TimeKeeping_29186184c---Copy.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If you now uncomment line 2
That comment of mine was associated with the volatile demo file that I posted here and in that workbook you'll find this code in Module1.
Function AddBelow(cell As Range)
'Application.Volatile
AddBelow = cell.Value + cell.Offset(1, 0).Value
End Function

Open in new window

To uncomment the line just delete the single quote from the beginning of the line.

And here's an update to the main workbook.
29186184d.xlsm
CERTIFIED EXPERT

Author

Commented:
I was looking at VolatileDemo.xlsm. I just found the AddBelow function. But not getting results yet as expected when toggle comment. Will get back and edit this post when I figure it out.
https://www.excel-easy.com/vba/examples/volatile-functions.html
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
That doesn't really affect this question though, but if you close this question I'll still be happy to follow up on the volatile demo if you need my help.
CERTIFIED EXPERT

Author

Commented:
Have to work today. What change did you make in version d? It takes me awhile to test since I do not understand what exactly I am doing to get the errors.

Obviously this setup is much harder than I thought given all the errors I had in the original and in the errors in the versions.

Thank you for solving this problem. I hope version d works. The layout you created looks very nice.

What caused the problem in version c?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
There's an Application setting called Calculation that determines if formulas are calculated automatically or not and I believe that somewhere in my development it got turned off.  I corrected that in 'd'.

Obviously this setup is much harder than I thought given all the errors...
I'm really sorry about that.
CERTIFIED EXPERT

Author

Commented:
Calculation - how does this cause the error?

Most of the time there is no error. What am I doing to cause the error? It seems random at the moment. I would rather the error to be deterministic. Knowing this would help me test better.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you still getting errors with the 'd' version? If so please describe the exact steps that led to the problem so that I can try to reproduce and fix the error.
CERTIFIED EXPERT

Author

Commented:
I haven't tested. I mentioned I don't know the steps. But as before I can send you the doc that shows an error.

Calculation - why did the c version produce the #Value error? Why didn't it not calculate to give a wrong answer rather than an error?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Just use the 'd' version as is and I promise I will stand by my work and if you find any problems I will fix them.
CERTIFIED EXPERT

Author

Commented:
I was just hoping to learn a little more about how to program vba.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Calculation - why did the c version produce the #Value error?
I believe calculation was set to manual and so my UDF would not produce any value until the formula in the cell was checked. The code now makes sure that it is set to calculate automatically.

Why didn't it not calculate to give a wrong answer rather than an error?
I don't believe it calculated at all, and when it does calculate something it always gives the right answer based on what it's told to do.

Do you have questions about the code?
CERTIFIED EXPERT

Author

Commented:
I keep thinking that I would have questions about the code. But I am swamped with work, and not sure when/if I will get to looking and learning. I entered numbers, made a mistake. Maybe I reentered - not sure. Please see attached. Thanks.
TimeKeeping_29186184d.xlsm
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Hi Martin, I had no idea that Excel was this complicated. Looks like it'll be a steep learning curve for me if I ever get to it. I'll try giving it another go in a day or two. Thanks again.
CERTIFIED EXPERT

Author

Commented:
Hi Martin,
I think this version is working. I hope this didn't take a lot of your time; or, if it did, I hope you learned something new.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018
CERTIFIED EXPERT

Author

Commented:
Here is my next related question caused by my company switching payroll companies.
https://www.experts-exchange.com/questions/29189434

Just when things were looking nice. :(

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.