• 0

Excel Time Sheet - Bi-Monthly time calculating overtime...headache


Question

I'm building an Excel program to replace employees just using MS Word to submit time sheets. We are paid on the 15th and the last day of the month. Our time periods run from the 11th through 25th and 26th through 10th. Time sheets are due on the last day of the pay period.

I am working on an Excel which each employee will fill out (on a shared network drive). My goal is to have our HR person open a master time sheet that shows names of each employee and the hours for that pay period. No time calculations are needed; we just enter (in decimal format) how many hours we work each week.

I have built this program and it's quite complex, but I'm having some issues. Most pay periods have 15 days. Some have 14 and more have 16. This causes my totals to be off. I'm attaching the Excel 2007 file so you can see what I mean (that's the only way you'll be able to).

I'm open for recommendations, even a PHP-based timesheet app as long as it will allow users to just go in at the end of the pay period and enter all the time in (without IN/OUT times). We need it to be simple for EUs to enter.

Don't worry about how it looks; it'll be cleaned up (cols hidden, etc) when it's ready.

2 things I'm asking here: 1) How can I make it work the way I need to? 2) How do I build a master file that links and updates a multitude of these files?

Master_Timesheet_Template.zipFetching info...

8 answers to this question

Recommended Posts

  • 0

I think I found a solution using the sumif function. Give me 10 minutes and I will have something.

*Edit*

I did it.

I slitted the two period in two columns. I have not checked the overtime tough.

Master_Timesheet_Template.zipFetching info...

Edited by Lewism
  • 0

That is a very interesting approach. I'm pretty sure I can make it work with OT and everything else. Thanks a ton! I'll let you know if I get it working and post the full file in case anyone else needs to use the same kind of thing.

  • 0

The same thing could be done with the overtime since you will probably have the same problem. And later you can hide those columns.

As for the master file, Excel let you link files.

=[Test.xlsx]Sheet1!$B$3

Lets say you use this formula in "Master Template.xlsx", it will go pick the data of Cell B3 in Sheet1 in Test.xlsx.

To create a link easily, open the two files and type = and chose a cell on the other file.

However, you will have a do this for each file that you have in that directory. I believe it could be done with VBA to automate this.

  • 0

Okay, I'm stuck again. I'm wondering if this is too complex for Excel and I need to use Access or something.

I'm wondering about the OT/Sick/Vaca time calculation and the primary Payroll Hours calculation. If you look at the formula for Payroll Hours, you will see some SUM calculations are off their proper ranges. The 14/15/16 day periods is moving the ranges improperly. It's as if we need a dynamic cell range to calculate based on an IF statement.

Oi this is complicated.

Here's an updated/modified version

Master_Timesheet_Template_2.zipFetching info...

  • 0

I've added a column that places the word Begin and the word End in the row that begins and the row that ends the pay period. I've set the Payroll Hrs formula to something along these lines:

SUM(IF(D5="Begin",L5,IF(D6="Begin",L6,If(D7="Begin",L7,If(D8="Begin",L8,"")))):L21

It dynamically chooses the beginning of the range. It's kind of exactly what I was looking for, I'm just not sure it's perfect.

Master_Timesheet_Template_3.zipFetching info...

  • 0

SOLVED! I'm pretty sure I got everything working. The long and short of the answer is that you can use IF statements to specify a range.

Ex: SUM(IF(C1=5,C1,IF(C2=5,C2)):C10)

I'm pretty happy with the outcome. I appreciate all your help guys.

Also, if anyone is looking for this kind of spreadsheet, go ahead and use it. I don't want to see anyone selling it though :)

Also, I added a formula that simply applied the words "This Pay Period," "Next Pay Period," or "Last Pay Period" for easy filtering. Filter that column and view only entries from this pay period. Woot!

Master_Timesheet_Template_Final.zipFetching info...

  • 0

I now have a finished product. I have cleaned everything up and made it pretty and usable, as well as (more importantly) setting up the admin sheet that links to the individual files. I used VLOOKUP in that book to pull only the 4 fields (hours, OT, sick, vaca) based on a date that's in the Admin file. You'll obviously need to update the links in the Admin file to your filenames, as right now it comes up #N/A.

I'm uploading both files, unprotected, so you can learn from my 20 hour adventure.

Thanks to everyone for their help.

SemiMonthlyTimeSheet.zipFetching info...

  • 0
  ttam said:
I now have a finished product. I have cleaned everything up and made it pretty and usable, as well as (more importantly) setting up the admin sheet that links to the individual files. I used VLOOKUP in that book to pull only the 4 fields (hours, OT, sick, vaca) based on a date that's in the Admin file. You'll obviously need to update the links in the Admin file to your filenames, as right now it comes up #N/A.

I'm uploading both files, unprotected, so you can learn from my 20 hour adventure.

Thanks to everyone for their help.

How do I adjust it for a pay schedule that ranges from the first of the month to the 15th, and 16th to the end of the month?

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Yeah, I've had a Recycle Bin on my taskbar since XP and till last year when I got a new laptop with Windows 11. It was especially useful for touch workflow - I could drag files into Recycle Bin from anywhere because my Taskbar is always visible, unlike desktop icons.
    • Not after SP1. There was a legitimate file copy issue prior.
    • Microsoft 365 security in the spotlight after Washington Post hack by Paul Hill The Washington Post has come under cyberattack which saw Microsoft email accounts of several journalists get compromised. The attack, which was discovered last Thursday, is believed to have been conducted by a foreign government due to the topics the journalists cover, including national security, economic policy, and China. Following the hack, the passwords on the affected accounts were reset to prevent access. The fact that a Microsoft work email account was potentially hacked strongly suggests The Washington Post utilizes Microsoft 365, which makes us question the security of Microsoft’s widely used enterprise services. Given that Microsoft 365 is very popular, it is a hot target for attackers. Microsoft's enterprise security offerings and challenges As the investigation into the cyberattack is still ongoing, just how attackers gained access to the accounts of the journalists is unknown, however, Microsoft 365 does have multiple layers of protection that ought to keep journalists safe. One of the security tools is Microsoft Defender for Office 365. If the hackers tried to gain access with malicious links, Defender provides protection against any malicious attachments, links, or email-based phishing attempts with the Advanced Threat Protection feature. Defender also helps to protect against malware that could be used to target journalists at The Washington Post. Another security measure in place is Entra ID which helps enterprises defend against identity-based attacks. Some key features of Entra ID include multi-factor authentication which protects accounts even if a password is compromised, and there are granular access policies that help to limit logins from outside certain locations, unknown devices, or limit which apps can be used. While Microsoft does offer plenty of security technologies with M365, hacks can still take place due to misconfiguration, user-error, or through the exploitation of zero-day vulnerabilities. Essentially, it requires efforts from both Microsoft and the customer to maintain security. Lessons for organizations using Microsoft 365 The incident over at The Washington Post serves as a stark reminder that all organizations, not just news organizations, should audit and strengthen their security setups. Some of the most important security measures you can put in place include mandatory multi-factor authentication (MFA) for all users, especially for privileged accounts; strong password rules such as using letters, numbers, and symbols; regular security awareness training; and installing any security updates in a timely manner. Many of the cyberattacks that we learn about from companies like Microsoft involve hackers taking advantage of the human in the equation, such as being tricked into sharing passwords or sharing sensitive information due to trickery on behalf of the hackers. This highlights that employee training is crucial in protecting systems and that Microsoft’s technologies, as advanced as they are, can’t mitigate all attacks 100 percent of the time.
    • Comments like these are genuinely fascinating to me because they're so far from anything I experience as a daily user of Win 11 since the first public beta. AI stuff? Have it turned off completely, never pops up anywhere. Forced MS account? Yes, they strongly recommend it and kinda push it lately during big updates and such, but it's still not forced. Pop up dialogs when you're not using Edge? Yeah, I vaguely remember seeing some reminders about using Edge a long time ago. I just clicked them away and kept using Vivaldi as usual (but frankly, I'd still much rather use Edge than Chrome - which I'm forced to use at work - I've grown to dislike Google a lot more than Microsoft lately, even if I am still deeply rooted in their ecosystem unfortunately). Awful context menus? A single simple tweak will get you the old context menus. Search in Windows using Bing? People use search in Windows for anything else than to search for local files or apps? Why? I just don't get a lot of the complains people have about Win 11.
    • Nice, but if you change the colour, the folder no longer shows image preview on the actual folder icon.
  • Recent Achievements

    • One Month Later
      Jdoe25 earned a badge
      One Month Later
    • Explorer
      Legend20 went up a rank
      Explorer
    • One Month Later
      jezzzy earned a badge
      One Month Later
    • First Post
      CSpera earned a badge
      First Post
    • One Month Later
      MIR JOHNNY BLAZE earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      618
    2. 2
      ATLien_0
      277
    3. 3
      +FloatingFatMan
      179
    4. 4
      Michael Scrip
      151
    5. 5
      Steven P.
      116
  • Tell a friend

    Love Neowin? Tell a friend!