Excel log sheet help

Status
Not open for further replies.
Joined
Sep 27, 2006
Messages
45
Location
Hunter Valley, NSW
I am trying to make a excel spreadsheet for entry and exit data but im a bit lacking in the excel area the things i want i am sure they can be done but i just don't know how to do them.

This is the situation i am at a gate and need to log all details for persons going in and out. What i am stuck on is excel doing a auto calculate the amount of time on site based on entry and exit time. Then at the end of the month doing a total hours report for visitors and contractors.

Any help would be great i have attached a zip file with a basic idea.

Also is there any paid programs that can do the same thing

Thanks ajs
 

Attachments

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Use "=SUM(I2-H2)" in cell L2, then right-click L2 and select "Format Cells...". Under the "Number" tab Select "Time" and then select "the format 37:30:55. That will calculate in hours:minutes:seconds the time on site.

You can then use the COUNT formula to add up the number of times a said member goes on site and then add their time.
 

fmon

Silent Key Jan. 14, 2012
Joined
May 11, 2002
Messages
7,736
Location
Eclipse, Virginia
Click in cell L3 to see formula after fx.

Change time to military units, eg, 12:08 to 1208 etc. use Rt click in cell to do this and select Format cell from popup. set dec to 0. Cells under H an I and L need to be changed to Number with 0 dec.
 

Attachments

Last edited:

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Here is the updated version. It automatically calculates the totals for the end of month report. There is a TON of stuff you can do with Excel. I really like this program and used it to make all kinds of cool automated reports.

I used a drop-down menu for the status column, which will allow the user to only select one of three items, Visitor, Contractor, or Officer. This makes the formula work on the Totals tab. It's all unlocked and can easily be messed up, but you can lock the sheets to keep that from happening.

I like to use the Conditional Format tool to have certain cells change colors depending on the value of other cells. Like if there is a policy that says that only 100 hours of visitors are allowed each month, then you can have the cell turn yellow when you are approaching 100 and then red once you hit the maximum of 100.
 

Attachments

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Click in cell L3 to see formula after fx.

Change time to military units, eg, 12:08 to 1208 etc. use Rt click in cell to do this and select Format cell from popup. set dec to 0. Cells under H an I and L need to be changed to Number with 0 dec.
If you change the format to number, then Excel will calculate the time incorrectly. For instance, if your Time In was 0845 and your Time Out was 1745, Excel calculates that to 900. It should be 9 hours, so the cells have to formatted using the Time category. It doesn't matter if he uses standard or military time at that point, as long as all of the time columns are formatted using the Time category and by selecting hours/minutes/seconds or by using the Custom category and making one that omits the seconds (hh:mm).
 

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Those are throwing errors because it is subtracting a high number (PM) from a low number (AM), so it is trying to produce a "negative" number.

You can overcome this by changing the format to add the date as well as the time. You'll have to enter more data into that cell, but it will work. You just have to make sure your employees are using the spreadsheet correctly.
 

fmon

Silent Key Jan. 14, 2012
Joined
May 11, 2002
Messages
7,736
Location
Eclipse, Virginia
I have been playing around and noticed with some of the times i get an error.
Those with # have checkout time (AM) prior to check in time (PM). Assuming this to be correct mil time (number) would show this as a neg number but would be correct exchange of time.
 

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Here is an update. The first cell (Date) is locked and is just copied from the data in column H. Once you put the data into a cell in H, the date will automatically fill in on column A.

I like military time as well, but I see you keep using standard time, so I formatted the cells for standard. Both spreadsheets are locked so as not to mess up the formulas, but you can easily unlock them to edit them in the future.
 

Attachments

Last edited:
Joined
Sep 27, 2006
Messages
45
Location
Hunter Valley, NSW
Thanks again. What would be easeir to use 24h or 12hr time. With the date being in the time area i can delete the date on the side cant i?
Also i keep getting 1/0/00 as the date would that be due to the bios date and a flat battery?
 

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
No, that's just how Excel handles dates and numbers. It assumes that if you leave the date blank, then the date will be January 1st, 1900.

There are other formulas that you can play around with to use two different cells (one with date and one with time) to do what you want, but it's just easier to input the date and time into one cell.

Edit:
When you enter data into that cell, you have to enter "11/12/11 21:12" OR "11/12/11 9:12 PM", otherwise, it will show the date as you said, 01/01/00. If you don't enter a date, you're going to run into the problem you spoke about earlier, i.e., negative dates (##############).
 
Last edited:

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Either 12 or 24 is easy for Excel, but I would rather use 24 because I was in the Navy.

You can delete the other date column if you want, but it makes it easy to see at a glance since the date is right beside the name. That is what is cool about Excel; you modify it the way you want it. The best thing to do is take a file and start playing with it. That's how I learned. Click on the help files and start making changes. You'll be amazed at what you can do.
 

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Okay, I did some more playing around with it and you can use just the Time column using this formula, "=IF(I2="","",IF(H2<I2,SUM(I2-H2),(I2+12-H2)))". That way, it should calculate everything for you and not produce any errors, unless of course they stay onboard more than 24 hours, then you'll get errors. The Time On Site column will remain blank unless Time Out in filled in.

I made two, 24 and 12 hour versions. It's much easier to use the 24 hour version as you don't have to enter AM or PM.

I changed the Date column back to normal, so you have to input the date there.

This should be a lot easier to use, instead of inputting the long date and time. Again, these sheets are locked to keep you from writing over the formulas, but are not password protected. Let me know what you think.
 

Attachments

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Okay, the more I look at this thing, the more mistakes I find...

So, I think I have it hammered out and have it good to go. I changed the Monthly Totals sheet to include several months. You can then add more in the future.

If you see or find any errors, let me know and I'll either fix it for you or show you how to fix it.

Later,
Scott
 

Attachments

fmon

Silent Key Jan. 14, 2012
Joined
May 11, 2002
Messages
7,736
Location
Eclipse, Virginia
If you change the format to number, then Excel will calculate the time incorrectly. For instance, if your Time In was 0845 and your Time Out was 1745, Excel calculates that to 900. It should be 9 hours, so the cells have to formatted using the Time category. It doesn't matter if he uses standard or military time at that point, as long as all of the time columns are formatted using the Time category and by selecting hours/minutes/seconds or by using the Custom category and making one that omits the seconds (hh:mm).
Based on 4 digit calucations in blue the solution in red would be 9 hours and 0 minutes using mil time.

However, your latest zip (nice work :)) appears to be best solution for charmingajs007. In my example the negative sign (-) would need to be ignored. Though I suspect an =IF formula could be devised to eliminate the -.
 

rescue161

KE4FHH
Database Admin
Joined
Jun 5, 2002
Messages
2,920
Location
Hubert, NC
Based on 4 digit calucations in blue the solution in red would be 9 hours and 0 minutes using mil time.

However, your latest zip (nice work :)) appears to be best solution for charmingajs007. In my example the negative sign (-) would need to be ignored. Though I suspect an =IF formula could be devised to eliminate the -.
Thanks! :)

You have to make it so people can't fat-finger in bad data, i.e., 1266 instead of 1255. Using a number format, that is a valid entry. If you enter 12:66 into a time formatted cell, it will change to 13:06. I made a new one with validation on the time entry. It keeps you from entering a time without the colon ( : ). Before, if you entered a time without the colon, it would display "00:00". Now, you just get an error box.

He'll have to unlock each one and add lines if he needs more than 100 entries.
 

Attachments

Last edited:
Status
Not open for further replies.
Top