1

First-time caller, long-time listener. I'm sorry if the title is a bit off, not really sure how to vocalize this issue. I have built a system at work for our work-study students to clock-in on. Unfortunately, the rest of our University is on a paper system and I cannot accept that. The system is made from two parts. The first part is a Google form where clock-in information is reported, it also includes a filter that separates that information based on pay period. The second is another sheet that imports that data and formats it into a printable timecard.

Here is the problem in a nutshell. We have students that work past midnight, potentially to 2:00 am. I need this system (or some system) to clock them out at midnight and clock them back in on the following day at 12:00 am.

Not even sure where to start with this one. I guess I need a script? A solution, advice or just a point in the right direction would be greatly appreciated. Thanks for your time all!

Form & Filter: https://docs.google.com/spreadsheets/d/19LfKUQY6etiRY2wuyFYM8Jzay7IGXrwn0rHKgDwnmE4/edit?usp=sharing

Pay info and Timecard format: https://docs.google.com/spreadsheets/d/1-2-7D7AHi6J-4cURlOsQOC1KznCR6zsejkTbyBFamjA/edit?usp=sharing

RadLad
  • 13
  • 2

2 Answers2

0

It sounds like a cool project! Here are some thoughts:

Using the Script Editor at Tools > Script Editor in Google Sheets:

  1. You can collect the clock in and clock out timestamps.
  2. You go through those cells in the code and if the day is different in between Clock in and Clock out, we know the student worked overnight. Now you do this:
  3. You take the clock in time and make the clock out time midnight on that day.
  4. You insert a new row after the Clock in row (sheet.insertRow()), and then in the new row you set the Clock In time to 0AM and the Clock Out time to the departure time.

I imagine people have already done this before and faced your same problem, so here are some examples I found online, although they may not address your specific issue:

Augustine C
  • 794
  • 6
  • 20
  • 1
    This is very helpful, thanks a million. I'm really just getting started with Javascript / App Script, so I'm going to hold off on fixing it until the end of summer (I'm taking a class). We don't have anyone working past 5 pm until next semester, so no biggie. The logic here is helpful. Thanks again. – RadLad Jun 06 '17 at 17:36
0

This might point you in the right direction. I check for a PM followed by AM. If it finds this condition, it splits it into two calculations. One up to midnight and the other on for after midnight. The next day looks back and if after midnight time occurs it picks it up. If no after midnight time occurs, it does what you do now. I think you can make this work (you will need to add a couple of columns which you can hide). I don't know what you would do on the last day of your time card. Below is an example you can copy:

https://docs.google.com/spreadsheets/d/1cO1ggY05kz70lfPE0YZCXaaa0AiaGKDM1s9FQPnw6Zs/edit?usp=sharing

Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • Thanks for your help Ed. Unfortunately, looking only at AM PM wouldn't work. For instance, if someone worked in the afternoon, then again the next morning it would match that condition. I guess I could try and look at that in conjunction with IN / OUT, but I like Augustine's idea of making a change at the raw data level/scripting. – RadLad Jun 06 '17 at 17:27