Tracking timecard hours automatically in Excel against two different online Timecard formats

For the new timecard is it true that you just want to track weekly and monthly total hours worked? If so how about a second "Pay Periods" sheet that would automatically reflect the hours added or changed in,  and/or pay periods added to the existing sheet? The second sheet would look something like this.

Any chance we could get everything done in one sheet? With two sheets, I have to look at four views to reconcile, but only three are available at any one time. So having all in one sheet is preferred?

Is there any chance to have the extra columns in the original sheet consolidated into one of the columns to keep the number of columns reduced? Then instead of having a header describing all the extra columns, there could be another column to the right of the numbers with a description (I know - repeating text over and over again - but it saves extra columns.)

Just sound-boarding. Something like my yellow highlights for the new information is what I was looking for. (I originally had another color for the monthly total to keep it clearer to me.)

The way I had it in the OP did result in a special case that I did not realize at the time. That is, One of my yellow cells would overwrite a Saturday (end of week) that also happened to be the end of a Time Period. But now that I saw that, I could probably lay it out a little differently on one sheet. Thanks.

Sure it can get done in one sheet but if so I'd like to add columns 'E' and 'F' in my picture as new columns. I know that total width of the columns is a concern for you but there are several things that could be done to reduce the width.

1. The Date column's width could be reduced
2. The date could be displayed as "Wed 07/09/20" (or similar)
3. Columns 'B' and 'C' could be made narrower and double (or more) hight and formatted so that the text wraps
4. Columns 'E' to 'F' could be made narrower and the headings written like column 'G' in this

or this

The way I had it in the OP did result in a special case that I did not realize at the time. That is, One of my yellow cells would overwrite a Saturday (end of week) that also happened to be the end of a Time Period.

That's why I suggested two sheets.

Re my #3. I just realized that columns 'B' and 'C' already wrap so they could easily be made narrower.

I see you have given this a lot of thought. I will try to do the same. I have a little time this weekend also. But, no rush. What I already have is great. Thanks.

Referring to the original OP sheet, some clarifications:
"Remaining avg-hrs/day" and "Days in PayPeriod"
These two definitions do not change. They  only apply to the original PayPeriod. No need to have visibility (I think) about these two fields in the new weekly PayPeriods. (Granted, the number of days in the first and last week might be different than 7 days/week, but I think that it is easy to see that since the weekends are greyed out.) Also, the "Remaining avg-hrs/day" helps me make sure that I am not falling too far behind in what is required for the Prime. (And it also tells me to relax a little if I get ahead.)

Making the first line narrower as you describe makes perfect sense. Now, with your proposals, you really got me thinking about spacing. Here are my ideas.

"Wed 07/09/20" certainly helps. I could even go with "SMTWTFS07/xx/20". Maybe I could name the sheet Pay Periods 2020,and leave out the "/20". (I do not think I will get confused with TWT or FSSM about which day the letters stand for since they are in sequence. If the "Add Pay Periods" button takes up too much width, OK with me to move it to B column, or even use a hot key (although I do like that button). If necessary, leaving out "Activity" or "Notes" and having that button is also OK.

Re: Adding "Monthly Total"
How does this idea sound: Instead of adding this column, just put the monthly total in the "Hours Performed in PayPeriod" (E) in the row between the last day of the month and your "Pay Period" row banner (e.g., "August 01, 2020 to August 15, 2020"). Coloring the background olive green makes it stand out as something different. The space is already free, so adding the monthly total there would be good. I wish I thought of that sooner.

Re: H "Days in PayPeriod" - This is the only field that is constant as I enter hours each day. It seems like a waste to have a constant take up a whole column. It could go (as some colored field or text color) in the one of the many empty cell in E, F, ..., or it could be a special column F, right next to the monthly total, except that it is also repeated in the middle of the month. Having it colored differently means to ignore the first row header (to me).

Re: Weekly totals - here is a catch. Recall that I said that there is a discrepancy between the number of hours between the two online timecards due to holidays. So I add 8 to the prime timecard to reconcile with my paid holiday from my company. Maybe that holiday can be taken into account. I would need to think more about PTO which I do not know in advance. That one is trickier.

I'm not sure I understand everything you wrote. Could you mock up a sheet for august that shows what you'd like me to automate?

With your narrow header approach and the following suggestions, I think this is understandable and not too wide.
The "Add Pay Period" button would have to move to Col B.
Col A date compressed per your idea.
Col A has "Days in PayPeriod" to left of the PayPeriod date range that is computed (greenish fill)
Col H has "Hours Worked in Week" (Yellow fill)
Col E has "Total hours worked in Month" (E135) in empty row following last day of the month (carker violet fill)
Maybe this Col E should be moved to Col H in the same row since Col H now represents the new Timecard. (but keep dark violet color).
One thing I kind of missed was that I lose my original total hours in the PayPeriod. Maybe Col C could have that number (=8*Col A) set in the same greenish color.

July Pay Periods have same style.
The first Aug Pay Period shows a way to remove Col H, but it would require a special case shown where the Pay Period ends in Sat. I guess keeping Col H is easier, so that is OK.
What do you think of this approach?

I like it, especially the idea of putting the problematic July monthly total on the otherwise blank row. Please verify that you've changed the requirement for column 'H'.

Sure, H seems to make sense. Thank you.

If you still have it, could you attach the workbook that contains the mock up?

I took the e version and just typed in some info like the dates or just replaced your formulas with a simple sum manually. And the formula were a a little different 4 the three time periods. That's why I showed a screenshot since the internals have no value. I'm sure I have it if you still want it. As you can guess, everything outside of the three time periods is quite a mess. Also when I was changing column A with text, I started getting the # value error but I guessed that was to be expected. So I just made very specific formula with hard coded numbers sometimes.

I do want it

Here it is.
29186184e.xlsm

Thanks. The attached workbook contains two pay period sheets: 'Pay Periods' and 'Pay Periods (2)'. The latter is the result of running the new 'Convert' sub. It copies the 'Pay Periods' sheet and converts it to look the way I believe you want. Please check out 'Pay Periods (2)' and if you find anything wrong or if there's something you want changed like perhaps cell fill colors let me know, but for now please don't click the 'Add Pay Periods' button since I have not yet modified the code that it runs.
29189434.xlsm

Thanks for the file.

1. re: E100: "Jun: 176"
I think the idea of moving it to H100, as we were considering, makes more sense. Changing color to match H makes it more consistent with H. Having a brighter yellow fill color and making text bold would make it stand out more.
I think just having "176" is enough, because the next line has July on it, so no confusion will occur. (And strictly having 2-decimal place numbers may prove useful and easier to work with in the future.)

2. re: Col H: Could the Light Yellow weekly sums include 8 hours for the holiday to better reflect the new timecard?

3. re: "or more if using Paid Time Off to reconcile the books." (I will use PTO if referring to this.)
Could there be a PTO sheet with date and number hours per row? (Could just be the Holdays sheet.) To clarify:
- If I get sick, I would make an entry with, say 8 hours. The yellow weekly totals would include the PTO (as well as the holidays). If I put in 8 hours for PTO today, but later lower it to, say, 0 or 5, then the Pay Period sheet would reflect the change.
- Holidays are shown as before showing the text and green fill as already done, so no change.
- PTO does not have any special coloring and the numbers are not explicitly shown on the Pay Periods sheet. Only the filled in yellow cells are affected by the PTO and Holidays.

 (I assume you know about the #VALUEs since you sent me the file.)

1. re: E100: "Jun: 176"
I think the idea of moving it to H100, as we were considering, makes more sense. Changing color to match H makes it more consistent with H. Having a brighter yellow fill color and making text bold would make it stand out more.
I think just having "176" is enough, because the next line has July on it, so no confusion will occur. (And strictly having 2-decimal place numbers may prove useful and easier to work with in the future.)

Sure, I was just following what you showed in your original picture.

• So do you want the weekly and monthly column 'H' color to be the same bright yellow?
• Should both weekly and monthly column 'H' fonts to be bold?
• Should both weekly and monthly column 'H' totals be to be 2 decimals?
• With the month total now in column 'H', would you like "Mo Total:" in column 'G'. If so should it also be yellow?

2. re: Col H: Could the Light Yellow weekly sums include 8 hours for the holiday to better reflect the new timecard?

• Should both weekly and monthly column 'H' totals include the holiday?

PTO

I think that should be the subject of a new question.

(I assume you know about the #VALUEs since you sent me the file.)

When I sent the file there were no @VALUE errors. Were they in column 'G'?

Thanks for getting back so quickly.

• So do you want the weekly and monthly column 'H' color to be the same bright yellow? -- Could the H weekly totals be light yellow and no bold and the monthly H grand total be brighter yellow and bold?
• Should both weekly and monthly column 'H' totals be to be 2 decimals? -- I can enter time as 3.25, for example, so 2 decimals is needed. A spot check of a couple of cells already show 2 decimals, so I do not aniticipate any changes needed, but I could be wrong.
• With the month total now in column 'H', would you like "Mo Total:" in column 'G'. If so should it also be yellow? - Not necessary, but thanks for the offer. Having blank cells as reserve is not a bad idea, IMO.
• Should both weekly and monthly column 'H' totals include the holiday? -- Please do include 8 hours for the holidays. Then the Yellow numbers will match one of my timesheets exactly.
• When I sent the file there were no @VALUE errors. Were they in column 'G'? - Yes, column G. On my laptop, here is what happens when I open your latest file for the first time. Initially, Pay Periods (2) sheet does not have #VALUE in G. There is an "Enable Editing" banner which I click. After clicking, I see #NAME? in col G. There is now an "Macros have been disabled Enable Content" banner. I click on it, and the #NAME? becomes #VALUE. I hit the X and I do not Save so as to be able to reproduce these steps if needed. I assume this is because this version has the Rahd function which you plan on not using anymore.
• PTO -- Ok, I will ask a new question on this after I close this question.

Considering the 4.1 hours worked on July 4th, is this all correct?

In my last screenshot, I showed 10 in Col A. Although there appear to be 11 workdays, one is a holiday. So for this PP, I am expected to work 10 full workdays (8 * 10 = 80 hours) - no more, no less. Cols F and G take this number into my original calculations. 10, in this case. July 16 PP shows 12, which is correct.

In line 101, could you please add "80" (=8*A101) also in light green in C`101.

>> Considering the 4.1 hours worked on July 4th
We had blank on the holiday lines up to now. Are you considering a case where I am allowed Overtime (OT), and taking that into consideration?

The light a bright yellow colors look nice.

One thought. Looking at your screenshot, if E116 > C101, then I have either made an error, or I have been authorized OT. In either case, could we change the text color of E116 to red so as to alert me of one of these two conditions?

I do not understand the three negative numbers in Col G.

If H135 is just the sum of the numbers for the month in the H column, then could we have a corresponding E1`35 that represents the two bimonthly totals in the E column (using blue instead of yellow)? (Although this number does not appear in the Prime's Timecard, I think it will be useful to compare the two monthly totals. If this is done, then the E monthly total will be 8 hours less than the H monthly total, due to the holiday.)

In my last screenshot, I showed 10 in Col A...

Corrected.

>> Considering the 4.1 hours worked on July 4th
We had blank on the holiday lines up to now. Are you considering a case where I am allowed Overtime (OT), and taking that into consideration?

No I'm not; I was just trying to understand what "Please do include 8 hours for the holidays" meant so could you please explain that?

...could we change the text color of E116 to red so as to alert me of one of these two conditions?

Almost anything is possible but I'd like to do all non-trivial changes in a new question or questions. But talking about making something made red because of an error, I assume that you would want to correct that error and have the red color go away, and while you could do that manually an 'Update' button when clicked could do it automatically. The reason I bring up the possibility of an Update button is that I recently realized that I've gone down the wrong path a bit when it comes to the weekly totals in column 'H'; they are now being calculated via VBA rather then by formula and while that works fine in the one-time conversion, it would not work when in production after adding new pay periods when you entered your hours. To make a long story short, would you mind if it were necessary for you to have to click an 'Update' after you made changes to a pay period?

I do not understand the three negative numbers in Col G.

The #%&$#! Rahd function! Please restate for me how the column 'G' values should be calculated.

Does it make it easier to avoid having a Convert function? If so, I can just add existing information manually to the final result.

Re: Yellow background for tracking the new 2nd timecard.
1. The new timecard always begins on the 1st of the month and ends on the last day of the month. Important thing to note is in the OP.
2. A week ends always on a Saturday.
These two timecard rules leads to bizarre effects as noted in the OP. August has 6 weeks! Notice that 1st week in August has only 1 day because the first day in August is a Saturday. The 1st week in July has 3 days because July 4 is a Saturday and July 1,2,3 are 3 working days, so if I work 8 hours on July 1 and July 2, then the yellow first week would be 24 hours because yellow includes Holidays (and in my next question, will include PTO). (In the Prime's timecard I just leave the holiday entry blank.)

H should include the

G: at beginning of time period, say, July 1, then G will have 8 since that is the expected hours per day to be worked to keep on track with the target goal of 8*10 = 80 hours. So 80/10 = 8. If, after 3 days, I put in 10 hours, then there are 7 days left. I owe 70 hours to get to 80. So G would have 70/7 = 10 hours/day. So I better start working extra hours per day or catch up on a weekend that is within the Pay Period. Once I meet or exceed the target goal, in this case, 8*80, then I have nothing to do, even if authorized OT. In this case G should be 0.

>> I assume that you would want to correct that error and have the red color go away,
If I put down for a day 22 instead of 12 and this caused me to exceed the target goal, then, yes, I would see the red and change 22 to 12. But if I am allowed OT, then when I exceed the target goal (shown in C101 = 8*A101), then the text goes red, and then no correction can be made. The red text in this case is just an indication that I have started to work the authorized OT.

>> it would not work when in production after adding new pay periods when you entered your hours.
This I do not understand. When I wrote my formula using SUM, whenever the Yellow filled cells changed, the total would change automatically. I may be missing something as I don't really understand what the conversion is about; and I do not understand why the add Pay Period button is causing problems in H since all the numbers are zero to being with. I would prefer not having an extra button. Is that OK with you?

If H135 is just the sum of the numbers for the month in the H column, then could we have a corresponding E1`35 that represents the two bimonthly totals in the E column (using blue instead of yellow)?

>> The #%&$#! Rahd function!
I thought you had a version that did not use Rahd. Maybe that solves all problems?

Does it make it easier to avoid having a Convert function?

Yes but the Convert function has helped me figure out exactly what you need. I will abandon it,

The new timecard always begins on the 1st of the month and ends on the last day of the month.

Currently when you click the Add Pay Periods button you can enter as little as 1 pay period and if you ask for 1 (or any odd number) the last new pay period will end on the 15th. Would you like it changed so that 1 would give you 2 pay periods (a whole month),2 would give you 4, etc? (It will make my job a tiny bit easier)

I thought you had a version that did not use Rahd.

Consider it gone.

>> Would you like it changed so that 1 would give you 2 pay periods (a whole month),2 would give you 4, etc? (It will make my job a tiny bit easier)
I was going to say I am happy with what you have. But if working with a month at a time makes it easier, then by all means, do so.

The new timecard always begins on the 1st of the month and ends on the last day of the month.

The main point here is that the yellow H column will not usually show an entire 7 day week for the first and last week of the month. August is a good example where the first week only has one day. As a result, if I were to put 2 hours on Sat, Aug, 1, then the Yellow H value will be 2 for the first week, even though the normal week runs from Sunday to Saturday. In this case all the hours of the normal workweek will be ignored except for Aug-1. Confusing? Yes! Clear? I hope so. Same idea applies to the last week of the month which may likely not have a full 7 days in it. But this explains why August has 6 weeks in the H column. Crazy! Yes! Clear? I hope so.

The last week in August will consist of two days - the 30th and the 31st.

Take a look at the attached workbook and let me know if you find any problems.

You'll find that the day name now has three letters and not two (eg "Sat 01" rather than "Sa 01"). I had to do that because there is no Excel two-character Day format option and so previously I was creating a string for the date rather than an actual date. The code does a lot of work based on the date and working with the string was too difficult.

You'll also find a lot of comment out code. Once you approve of the results I'll clean that up.

I added an 'Holiday Name' column on the Holidays sheet. It's not used in the code but you could use it for documentation or just delete it.

Currently there's no validation of the hours worked and values like 7.12 or 16 or 25 could be entered. That's something that could be fixed in the future.

A final thought; column D's heading says "Today's Hours" and they're really not. Maybe "Daily Hours" would be better.
29189434a.xlsm

Thanks for the file.
"Sat 01" - looks fine. I do not know what is easy to do.

'Holiday Name' -  a nice touch

no validation of the hours - That is fine with me.

"Today's Hours" vs "Daily Hours" - Either way. I do not see the difference.

Add Pay Periods: Selected all defaults:
Entered 8 hours for each row in D for the first pay period in august. But the monthly total, H36, is 0.

Started over. Add Pay Periods: I asked for 2 starting 6/1/20:
Entered 9 in D3 - ok
Entered 8 in D4 - got  run time error 1004, Application-defined or object-defined error. Do you get this?

    'new
    ' Calc new weekly timecard hours worked
    For lngDay = PreviousSunday(Target.Row) To Saturday(Target.Row)
        If Not IsEmpty(Cells(lngDay, "A")) Then <-- Debug pointing to this line

Select allOpen in new window

Could you please have C10 have 8*A1 = Number days expected in pay period?
Could you also add in E36 the monthly total which will be the same as H36 if no Holidays?

Reported problems corrected.

1. Starting with a sheet with no pay periods I tested as follows:
2. Requested 2 pay periods
3. Entered 08/01/2- as starting date
4. (after each addition I tested everything I could think of)
5. Requested a 3rd pay period
6. Requested a 4th pay period
7. Deleted all pay periods
8. Requested 1 pay period with the same starting date
9. Deleted all pay periods
10. Repeated step 2 to 9 with 06/01/20 as the starting date

29189434b.xlsm

I will try it out. Curious. Why did June 2 pay period have a run time error?

What did your convert function do?

When I started with my original file that had my old style from March 29 to May 31, the add button allowed adding June pay period. Now this does not happen. Is the convert function related to this?

If appending a pay period to the end of the current listing is problematic, then I will just use one sheet for March April and May in 2020.

Why did June 2 pay period have a run time error?

There was a problem with the PreviousSunday function; if there wasn't a Sunday before the first data row the function would return 0 as the row of the previous Sunday..

When I started with my original file that had my old style from March 29 to May 31, the add button allowed adding June pay period. Now this does not happen. Is the convert function related to this?

I don't believe so since PreviousSunday would find a valid previous Sunday row.

If appending a pay period to the end of the current listing is problematic....

Try it:, but I don't think it is.

In the AddPayPeriod sub there's a missing variable,  Please add Dim lngDay As long.

I just tried appending pay periods to the end of your old data and it does work, but in order for it to work properly you must start by adding just one pay period.

Here is what I did:

• In AddPayPeriod sub fix a missing variable by adding "Dim lngDay As long".
• Save
• I copied original text A2:H65 covering March, April, and May to new Excel doc at A2

Get popup:

"Run-time error '91': Object variable or With block variable not set"

Select allOpen in new window

Hit Debug button:

If Not Intersect(Target, Columns("D")) Is Nothing Then
    ' Find the first row of dats that has the new format
    Set rngStart = Columns("B").Find(What:="Pay Period:", MatchCase:=False)
    If Target.Row < rngStart.Row Or Target.Row > ActiveSheet.UsedRange.Rows.Count Then  <- Pointer here
        Exit Sub
    End If

Select allOpen in new window

If you are comfortable doing so, please attach a workbook that just has your existing pay periods.

There is an Excel doc here:
https://www.experts-exchange.com/questions/29189434/Tracking-timecard-hours-automatically-in-Excel-against-two-different-online-Timecard-formats.html?anchorAnswerId=43129185#a43129185

But I do not think you need to use it. In your latest empty doc, I entered 5.5 in D3 (which is Monday) and got the error.

No error when I did it.

Oh, now I understand what you meant by first adding one pay period.
Up till now, I would copy all of March April and May in one shot into the new format and then hit add pay period.

I think now I have to do 5 separate copy and paste for the 5 pay periods with the first one being March 16 instead of March 29 as is currently the case.

Then I have to copy and paste five additional pay periods covering June July and half of August.

Is there an easy way for you to add a pay period after I do the March April May bulk copy in one shot?

Then  I could add 5 pay periods for June July and half of August and possibly copy what I already have into those fields in one shot.

Granted, the a column with the longer day and date format would be all pound signs, but that is okay with me.

The reason I asked for a workbook that contains your current hours is that I think I should be able to add a few new pay periods to it and then you won't need to do the copy/pasting. That is unless you want to do it so it all had the same format.

Columns B and C have my proprietary notes in them so I not allowed 2 distribute them.

I could put "begin new format" in one of the columns to mark where the first new pay period should begin if that helps.

Does VBA have a static variable, that is one whose value remains intact even after a function returns? If so, then "first_time" could be set to 1 and then the add period button would search for the phrase and add the new entry afterwards. Then first_time is set to 0.

Before I answer that let me ask you a few questions.

Would it be helpful to you if I provided an automatic  process which would copy the data from your existing pay periods sheet into an initially blank 'Pay Periods' sheet that would have the new format? If so is it true that your current pay periods sheet starts on March 29th?

To keep things simpler, I think I will just have two sheets. The old one and your new one

If I want the new sheet to be labeled 2020, then how do I start another sheet labeled 2021 and then add a pay period?

Would the 2021 sheet start on Friday 1/1 or would it start on Saturday 1/2?

All subsequent years start on Jan 1, 202x.

The first and last week of the month usually will not have 7 days in the week.

Just wanted to let you know that some time today I should have code that will automatically create and add pay periods to new worksheets 2021, 2022, etc as needed.

Okay here it is. This version checks to see if a requested pay period's year is greater than the year for the previous pay period. If it is then a new worksheet is created for the new year and the "Add Pay Periods" button on the existing sheet is hidden so that new pay periods can not be added to a "full" sheet. If you want to unhide the button (I can't imagine why you'd want to), go to Home->Find & Select->Selection Pane... and click the little square next to "btnAddPP". Note that the code depends on the pay period sheets being named exactly 2020, 2021, etc.

For example given a 2020 sheet with some existing pay periods, if you request enough pay periods so that the year stretches into next year, a 2021 sheet will be automatically created and the remaining requested pay periods placed in it.

The hidden Template sheet is used for the creation of new worksheets and pay periods, so if you want to change the headings for new pay period worksheets or change the formatting of new pay periods it should be done there. I've added a legend that describes what each row in that sheet is used for. If you have any questions please let me know.
29189434c.xlsm

All I did so far is put in June Daily Hours. When I copied and pasted the entire hours from the older timesheet, the weekly totals were sometimes 0. I had to zero out some times and put back the original hours and then the weekly totals looked ok.

But the monthly total is incorrect. See attached excel doc.
June_Totals_error_29189434c.xlsm

The monthly total is correct.

In the June_Totals_error_29189434c.xlsm file, I have two pay periods in June. They both have 88 hours. So the total hours worked is 2x88. That is the total number of hours I worked that month, not 155.5 hours.

Could you make the monthly total a sum() formula, and also include it for the other column so that I can see that the two sum's are the same. Also, where it shows 11 days, could you add in C col on same line a formula, =A(...)*8 to show the target hours expected for that pay period?

Adding these checks would be very helpful:

• To the line that has "11" days in the pay period, could you add 8*A(same row) in Col C to show the expected target hours for that pay period?
• To the line that has the monthly total, could you make it a H col =sum(...) and then use a similar sum in the E col for another monthly total. When there are no holidays, these two sums of monthly totals should be the same.

Oh, I see. For the monthly total I was adding up what was in the light yellow cells in column 'H'. I'll change that to add up what's in 'D'.

Adding these checks would be very helpful:

I already done a lot here, Let's deal with that later.

The H monthly total has to be only related to the H column. The light yellow sums have to add up to 88x2.


If you use the D column then the July total will be short by 8 hours due to the holiday.

The first and last week usually do not have 7 days in a week.

The light yellow sums have to add up to 88x2.

In the workbook you attached, are you expecting to have a light yellow cell in H34? If so then if a new pay period were added and 4 entered into D38 what should the total in H39 be? In the current scheme of things it would be 24.5 the sum of that complete week (D32 to D40) since it was my understanding that if the flight yellow totals should always be the total of Sunday to Saturday.

>> In the workbook you attached, are you expecting to have a light yellow cell in H34?
Yes, H34 is the last day of the last week in June. In general, the last day of any month also defines the last workweek. That is why I mention that usually the last week of the month does not have 7 days in it.

>> If so then if a new pay period were added and 4 entered into D38 what should the total in H39 be?
To answer this question, I hit Add Pay Period for one more PP for July 01 to July 15.
Wed 01 is in A37 and is the first day of the first week in July. The first week in July has 3 work days

Sat 04 is in A40.
If I enter 4 hours in A37, then the light yellow cell in H40 will show 12 hours (4 + 8) since Fri July 03 is marked as a holiday and gets an automatic 8 hours for that week. (It doesn't have to show the extra 8 immediately. That is up to you. Just as long as the 8 hours are included in the first week after I enter the hours for Wed and Thur, the 8 hours show up. Probably easiest if the new PP just shows 8 immediately since the holiday is known to be in that PP.

>> my understanding that if the flight yellow totals should always be the total of Sunday to Saturday.
This is not usually the case for the first and last weeks of a month. (It is always true in February on non-leap years.)

I believe I've already done too much for one question so if you haven't already done so, add enough pay periods so that they encompass next year and if that works satisfactorily for you ignoring the problems you mentioned, please close this question and add a new one.

Just as a reminder, in the OP is a clarification of what a week is in the new timecard:

Looking ahead at August, I see 6 weeks. (I know - hard to believe).

Week 1: 8/01 - 8/01 (week always ends on a Saturday)
Week 2: 8/02 - 8/08
Week 3: 8/09 - 8/15
Week 4: 8/16 - 8/22
Week 5: 8/23 - 8/29
Week 6: 8/30 - 8/31

In August, there should be 6 light yellow cells, one for each week. Most weeks in a month end on a Saturday, but the last week of the month usually does not end on a Saturday. In August, 2020, according to my new timecard, the last week of the month ends on a Monday.

As you see, week 1 has only 1 day in it, and if I do not work that Saturday, the first light yellow cell will have 0 in it.
Week 6 has only 2 days in it. The light yellow cell will sum up those two days.

Thanks. I got the idea somehow that a week was always a week of 7 days, I now understand that that's incorrect.

It was partly my fault for not showing a spreadsheet with all the peculiarities of this new timecard. I was having trouble reconciling the prime's timecard and my company's timecard, and my own excel document. So it is easy to understand how confusing these rules are. I mean, who ever heard of a 1 or 2 day week. Yet, here it is:

Have you tried adding enough pay periods so that it creayes 2021?

I just did that per your request. Just picked some numbers at random. A spot check turned up this:

Sun 30	
	
11	Pay Period: January 31, 2022 to February 14, 2022
Mon 31
Tue 01

Select allOpen in new window

and another check:

Sun 13	
Mon 14	
	
11	Pay Period: February 15, 2022 to March 01, 2022
Tue 15	

Select allOpen in new window

I then went to 2021 to see where the problem begins, and I found this:

Sat 15	
	
10	Pay Period: May 16, 2021 to May 30, 2021
…
11	Pay Period: May 31, 2021 to June 14, 2021
Mon 31	

Select allOpen in new window

May has 31 days.

If it is OK with you, let's get the light yellow issue fixed first. Then I will be able to start using the latest doc for this year. Who knows. Maybe they will come up with a 3rd timecard replacing the latetest one before the year's end. :(

In my previous post it shows that the second pay. In May goes from May 16th to May 30th. That is a problem because the last day all of the PP Should be May 31st.

Then the first day of the first PP should be June 1st, not May 31st.

In my previous post it shows that the second pay. In May goes from May 16th to May 30th. That is a problem because the last day all of the PP Should be May 31st.

Then the first day of the first PP should be June 1st, not May 31st.

I saw that and I'm in the process of fixing it. I believe it's only a problem when a new sheet is created.

Ok. But if you fix the light yellow problem first, I can start using the sheet immediately and that would be great.

I don't need 2021 for 5 months.

Where is the Add Pay Period button?

Oh. I was testing adding a new sheet and forgot to unhide the 2020 button.

 go to Home->Find & Select->Selection Pane... and click the little square next to "btnAddPP".

I am more than happy to not have a hide/unhide option. Could you remove that option. Vertical spacing is not much of a problem for me. (Just horizontal spacing was the main concern.) I understand the reasoning.. If I select 1/2 year of PPs then no need to see the ADD PP button. But then I will forget how to reinstate it. So let's keep it always viewable. We can make the button and font really small, or even add it to an existing ribbon if you prefer.

The way it works now (ignoring my testing oversight) is that the button on a sheet visible until the sheet is full. When the sheet becomes full the button is hidden so that you can't (without unhiding the button) make the mistake of trying the add more PP to, say, the 2020 sheet that ends with 12/31/2020 while sheet 2021 exists. That would wouldn't work because it would tell you that 2021 already exists. In this scenario I could delete the button instead of hiding it if that's what you'd prefer.

I don't understand why you say "If I select 1/2 year of PPs then no need to see the ADD PP button" which I interpret as meaning that once you've added up to June on 2020 that you don't want to be able to add more. What am I missing?

>> What am I missing?
Given your explanation about hiding, this last point was just a guess on my part and you can ignore it.

As long as the Add button is always available automatically without my having to remember how to do some special operation in the future, then that is fine. If it is hidden because there is no more room in 2020, that makes sense.

I believe that once the holidays have been read, then if they change the holidays during the year that I have already setup with empty PPs, then I would have to delete the unused PPs and then reapply them to get the holidays read again. But not a big problem because I don't think they will be changing holidays.

If I delete a PP in 2020, but there is also PPs already established in 2021, then should I delete the 2021 PPs as well in order to unhide automtially the Add PP Button?

No, just do this:

 go to Home->Find & Select->Selection Pane... and click the little square next to "btnAddPP".

As for the holidays, in a new question it would be possible to have the recurring US and state holidays automatically processed and the Holidays worksheet would not be necessary unless you wanted to use it for such things as vacations or other planned time off.

Could you make the default day the 1st of the month? I will always be adding one month at a time to match the newest timecard. In fact, I thought you were going to only put in one month at a time since you said that "It will make my job a tiny bit easier" to do one month at a time. But no big deal. I will use what you have.

The light green on July 03 is as expected. Could you add by default the 8 hours in Col H for the holidays? Then the spreadsheet will immediately match the timecard.

If you do not want to do either 1 and/or 2, let me know, and I will do more extensive testing by adding in real data. Thanks again. Very close to the polished product. (I know I cannot remember the rules for showing the Add PP button, but as long as I have access to this thread, I will be able to figure it out.)

(BTW, not sure what the Holiday's B column was for. I entered names and it has no effect.)

I put in quick random hours in June and July - no problems!

Please test the workbook as is and if it works then please close this question. After that I will be happy to make any modifications that you want including one that would make restoring the Add PP button easier.

Holiday's B column was just for you to use as documentation of the holiday's name. It's not currently used in the code. If that's not useful then just remove the heading.

I’m glad I was able to help and I'm looking forward to working on this again.

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

Thanks again. I Will start populating the new document by this weekend. I have confidence that all Will go smoothly.

We certainly have spent a large amount of time building and debugging this spreadsheet, and I thank you for that.
I think things are good enough for 2020. When I add PP into 2021 (a couple of different ways - e.g., add 6 PP; then add 26 PP; or add PP to Nov, then add a lot more PP to get into 20201), then I get the the popup:

Run-time error '1004'
Paste method of Worksheet class failed

When I hit Debug, I get:

I have run out of time given my increased workload and supporting studies. I know you said you tested this 2021 issue, so it works for you, but not for me. I may just have to live with what I have. It is helping a lot.

I apologize for the problems you are having. When I start with a fresh 2020 sheet and
Click the button
Accept the default 6 pay periods
Enter 01/01/20 as the start date -> 6 pay periods are created
Click the button again
Enter 26 for the pay periods wanted
Click Yes when asked the "Have you updated the holidays..." question

everything works as expected. One small change you could try would be to change the wsPP.Activate line to wsPP.Select

>> Enter 01/01/20 as the start date
 I don't see how this matters but with a fresh 2020 sheet, I started with June 1st 2020. Maybe you can see the problem that way.

About holidays, You said to answer yes. I tried both ways. No difference.

There were other problems.  after I got the problem I deleted the 2021 sheet and deleted the last PP in 2020. The add button was desensitized.

Starting at 06/01/20 made no difference for me in that everything worked as expected, If you select "No" as the answer to "Have you updated the holidays..." the new sheet should not be created. Was that your experience?

There were other problems.

Like...? And if the code gets interrupted by an error the Application.EnableEvents setting can be left set to False and in that case the button won't react to clicking it. To reset it to True, go to the Immediate Window, type Application.EnableEvents = True and press Return.

BTW I assume that in your testing that you are clicking the button rather than directly running the code and that you do it "hands-off". In other words you don't select a different sheet or anything else like that while the code is running.

Let's try this one last time.. Send me an empty file that works for you. I will try it on two different machines. Windows 10 Pro (my work machine that has webroot on it), and a Windows 10 home that has Windows Defender on it. Maybe something is different. My testing has been only on my work laptop.

Please use a distinctive different name for the uploaded file.

>>  you don't select a different sheet
After the error, I deleted the 2021 sheet.

I don't remember the other scenarios exactly. But I will keep better notes this time around. Thanks.

Here it is.
WorksForMarty.xlsm

Appropriate name!!

1. On Pro, got to Nov-2020, then hit 4 PP. Same error.
2. After error, an empty 2021 sheet was created. I deleted it, and was able to get the Add PP button back in view. Tried again, but same error.

Now, on my home computer with plain old WIndows 10 Home.
This particular problem is not present!!

Unfortunately, I am not supposed to take notes on  my home computer.

What am I to do? When I open the excel doc the first time I get an "Enable Editing" button. I click it. Then I get an "Enable Content" button. I have been clicking that also since it also says that Macros are disabled. Do you get that also. (Maybe not, since you are actually developing.) But if you download on another laptop or PC, do you get that in Windows 10? Do you think this is somehow related to the problem?

Do you have any idea how to fix this problem? What is so special about creating a new sheet in my Windows 10 Pro laptop that could cause a problem. Maybe you could see if there are any special commands that might require priviliges that are denied on my laptop.

When I wrote a MATLAB script that worked on two workstations, but not on a third, I changed the modern instructions to a set of simple-minded ones, and it worked on all three workstations. Maybe something like that is happening here? (One was Windows 8, other two were Windows 10.)

I don't have the same environment as you so I can't guarantee that this will work but I hope it does. If it doesn't you'll need to ask a new question so as to get other Experts involved who have an environment like yours.
HopeThisWorks.xlsm

Sorry, The last file gave an error. Do you have any commands that are Windows OS specific? I will try running the latest on my Windows Home PC when I get a chance.

Looks like a question down the line for the Windows OS experts. But where do I begin in identifying possible VBA issues?

But thanks for what I have. I have started  using the last version that ends in a 'd", and it is helping me track my time very well against two time cards. Well done!

I'm disappointed that that didn't work.

But where do I begin in identifying possible VBA issues?

You don't need to. Just create a new question attaching my latest workbook and explain that it works with Excel 2010/Windows XP but not in your environment and specify that environment.

Obviously, I hadn't tested holidays thouroughly since they occur so infrequently. I worked a little today (labor day), so I had regular time and holiday time. Maybe I am doing something wrong, but I am not getting good numbers to show that one total is 8 hours more than the other total. I will look into this more with a fresh download to make sure I am not messing up. But other than this glitch, the spreadsheet has been very helpful. It is late, so maybe tomorrow with fresher eyes, I will see whether I am doing something wrong. If I am not doing anything wrong, then please let me know what you see.

If I stop working weekends, I will write about the Windows 10 Pro with WebRoot vs Windows 10 Home with Defender causing that run-time error.

If you tell me or show me what days you worked and what hours you worked on them and what the totals should be then maybe I can see what's wrong.

I hope you can see what is wrong.

The attached sheet shows that I worked 1.5 hours in Week 2.
The 32 hours in E17 is correct - this matches the Prime's timecard.

But the yellow highlighted items do not match my company timecard. It is missing the 8 hours holiday.
My company timecard shows 9.5 for week 2, and 40 hours total; but the attached sheet just shows 1.5 hours and 32 hours, respectively.

From the OP:
"Pay Periods had a varying number of days depending on how many weekends and holidays were in the Pay Period."
"A holiday in a Pay Period always results in different cumulative hours between these two online timecards."
"Keep in mind that if there is a holiday, then I put 8 hours in for my company's timecard, and leave the holiday blank in the prime's timecard."

Thanks again.
HopeThisWorks---HolidayProblem.xlsm

To verify - H14 should be 9.5 because you worked 1.5 hours on the holiday and you seemingly get a bonus 8 hours because of the holiday, correct?

If you didn't work on the holiday should H14 be 8?

Currently the column 'H' totals are calculated via a simple Sum formula that you'll see if you click on one of them. If I have to include extra holiday hours for one or more holidays in a week then I'll have to use VBA to calculate those totals. Is that OK?

>> H14 should be 9.5
Yes

>> If you didn't work on the holiday should H14 be 8?
Yes

>> use VBA to calculate those totals. Is that OK?
Yes. But here is a thought and not sure if it is doable or makes it easier or harder...

1. What if 8 hours were included in the holiday at H9.
And then H14 would be:
=sum(d8:d14) + xxx
where xxx could be
   sum(H8:H14)

Or,
2. Maybe xxx q8 * (number of green colored cells in the D-range)

What would work?

Your suggestion #1 gave me an Idea. I first thought that if I put 8 in H9 when the holiday row was added that I could change the formula to =SUM(D8:H14) but that gives a circular reference error. But I could put 8 in column 'Z'  (or any off-screen column) of the holiday row and change the formula to =SUM(D8:D14,Z8:Z14). I could also, if you like, put 8 in H9 for documentation purposes and since the formula would still be =SUM(D8:D14,Z8:Z14) there wouldn't be a circular reference error.

Does that sound good? If so:

1. Is 'Z' a good column to use?
2. Should I hide column 'Z'?
3. Do you want me to put 8 in H9 for documentation purposes?

Actually the column doesn't need to be off-screen, It just needs to be after 'H', so even column 'I' would work.

>> Is 'Z' a good column to use?
I like your 'I' suggestion better.

>> Should I hide column 'Z'?
I like your idea of not hiding the '8' for "documentation purposes".

>> Do you want me to put 8 in H9 for documentation purposes?
Based on the first two ideas you had, it is no longer necessary to document the 8 since it is already shown in 'I'.

Seems like sound-boarding is bringing things very close to the horizon. Looking forward to seeing the result. Can we call the "I" column, "Extra Hours"?

Thanks.

Since you want to show the extra hours in column 'I' I assume you want the formatting of the Pay Period heading row and the right-hand border formatting moved from column 'H' to column 'I'.  That will take some work that I'm willing to do but please ask a new question.

Sorry. I didn't mean to cause a lot of work.

If I understand you correctly, you are talking about moving the right most border on H over to I. Is that correct? If so, it is true that would make the page look more professional, but I am primarily concerned about tracking the two timecards.

I just thought your suggestion

so even column 'I' would work.

was a good one.

I do not even care if you leave out a label. As long as the numbers are correct, I am very pleased with what you have produced.

I'm working on the extra hour modification and I notice that when a pay period is added that contains a holiday (like 9/1 to 9/15) that Avg hrs/day Left defaults to 8. Should it do that?

Are all the totals correct?

Looking very good!

>> when a pay period is added that contains a holiday (like 9/1 to 9/15) that Avg hrs/day Left defaults to 8. Should it do that?
Yes. At the beginning of each pay period, I am expected to work 8 hours a day for the Prime (excluding holidays, of course),
so 8 hours for "Avg hrs/day Left" should initially be 8 whether or not there is a holiday.

i am curious as to why there is an 8.00 in G17 and G51, but not in G34 and G69.

> You may also notice that I added a module named modChangeLog
Sorry, I did not. This is mostly Greek to me.

>> In this version I:
I did not see version I. Could you please upload it.

>> In this version I:
I did not see version I. Could you please upload it.

Sorry for the confusion. In this case "I" is not a version letter. It just refers to me. It would have been clearer if I had said "In this version I did the following:"

> You may also notice that I added a module named modChangeLog

It's not all that important but if you go to Visual Basic (Alt_F11) you'll see modChangelog in the Project - VBA Project window. If you double-click on modChangelog you'll see the contents that I show at the left of this picture.

Re: I
Oh that's funny. I see what you meant. Actually if you left out the I, then the action verbs in the bullets would have to have an implicit subject of "I" since I would have no trouble excluding myself as the subject; and as there is no-one else in this thread, then the subject of  bullets would have to be you.

I think Version f may be great! I filled this version up from Jun-1 to Dec-31. I copied Cols B,C,D a Pay Period at a time, and I think the numbers are coming out correctly. When I get to my timecard later, I will check the sheet against those numbers. Thanks for fixing this up. Looks great! I have not done the usual amount of testing that I do, but based upon what I see, I will be surprised if I find anything wrong with 2020.

Thank you again, Martin.

You’re welcome and 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

Your program has helped a lot! I submitted the 2021 problem in case you can reproduce the problem and fix it:
https://www.experts-exchange.com/questions/29203292/Excel-Spreadsheet-Error-on-Windows-10-Pro-with-Webroot.html#questionAdd

I recently lost my windows software [cry] and while I'm currently trying to get it back, it could be a few days before I can do any programming.

You lost it? How did that happen? Do you remember where you last left it? I would carry it with me on a chain.

I was using Windows XP as a virtual machine on my mac via Parallels Desktop. When I updated my macOS from 11 to 11.1 it killed my VM. Recovering it should have been easy, however while I thought I was backing up the needed files, I wasn't, so it's my own damn fault. I did have a Windows XP backup from 2016 and that's what I'm using now, but it's painfully out of date (even for ZP!), I have installed an ISO of Windows 10 and I'm debating with myself wether to plunk down $200 for a license key. If I do that I'll take a look at your problem.

I updated the Excel version, and shut down/power up.

Everything worked! I was very surprised. After checking versions, my Windows 10 Home Excel is 32-bits. Not sure why my son installed that instead of 64-bit. (Less money, maybe.)

Let me know when you are ready to add features. The timesheet has been extremely useful for me. I forget what might be useful. It saved me a lot of headaches when trying to verify that the two timecards and your spreadsheet jived.

I got the details worked out, I hope.
https://www.experts-exchange.com/questions/29210400/Part-3-Tracking-timecard-hours-automatically-in-Excel-against-two-different-online-Timecard-formats.html#questionAdd