| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel as Attendance Tracker
Merry Christmas Everyone!! I've posted this on another board - got lots of views but no responses so I'm hoping Santa will be good to me and someone reading this thread will have an answer. We have 100+ employees. We need to track PTO, Vacation in hours and then also log other instances of time away from work (f= fmla, t=travel, c=comp time, h=work at home). However these "other instances" do not need to be tracked in terms of hours used. The only suggestion that came on the other board was to have 3 rows per employee - not a good solution. My solution so far has been to have 3 columns for each day but this is causing me to run out of columns before I get to April. I thought I could use SUMIF but apparently I'm wrong. Why can't I put something like "8v" (indicating 8 hours of vacation used) and then have my totals column look at the range and sum if it says 8v, the column next to this would sum if it said 8p. Any help would be appreciated. -- Donna123 ------------------------------------------------------------------------ Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
I'm not an expert but it think you could. Try this, in a blank column input some different numbers like 8v, 8p, 7v,4p and so on, next put this formula in any cell, just make sure you alter the formula to suit your range: =SUM(IF(RIGHT(B1:B10,1)="v",--LEFT(B1:B10,LEN(B1:B10)-1))) this formula will look at a range of cells, if the cells ends with "v" it will add them to the total, you will see that the formula only adds those cell that ends with "v". this is an array formula so you will have to enter it with CTRL+SHIFT+ENTER, just hitting the enter key will not work. Hope this Helps! JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
How about one column per employee? One row for vacation, one row for
holiday, etc. Whenever hours are expended, just update the appropriate cell. -- Gary's Student "Donna123" wrote: Merry Christmas Everyone!! I've posted this on another board - got lots of views but no responses so I'm hoping Santa will be good to me and someone reading this thread will have an answer. We have 100+ employees. We need to track PTO, Vacation in hours and then also log other instances of time away from work (f= fmla, t=travel, c=comp time, h=work at home). However these "other instances" do not need to be tracked in terms of hours used. The only suggestion that came on the other board was to have 3 rows per employee - not a good solution. My solution so far has been to have 3 columns for each day but this is causing me to run out of columns before I get to April. I thought I could use SUMIF but apparently I'm wrong. Why can't I put something like "8v" (indicating 8 hours of vacation used) and then have my totals column look at the range and sum if it says 8v, the column next to this would sum if it said 8p. Any help would be appreciated. -- Donna123 ------------------------------------------------------------------------ Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
PinMaster, Your formula seems to be getting me closer to something - at least its showing a zero in the field. If nothing else, this tells me excel CAN do this, I just need some fine tuning to the formula is all. Any ideas as to why it would come up as zero? I put the formula in exactly as you showed: =SUM(IF(RIGHT(B1:B10,1)="v",--LEFT(B1:B10,LEN(B1:B10)-1))) -- Donna123 ------------------------------------------------------------------------ Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
Don't know why the "0" is displayed, however, if you Format/Cells/Number/Custom and enter a "#" in the Type input box for the cells that are affected you will eliminate the "0"'s. -- Lost4Now ------------------------------------------------------------------------ Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
It may be that your data was not in the range B1:B10 or you did not enter the formula as an array formula, array formulas need to be entered using SHIFT+CTRL+ENTER. One way to find out if it was entered as an array is to select the cell with the formula and look in the formula bar, if the formula is surrounded with brackets {} then its an array formula. This is how the formula should look like in the formula bar: {=SUM(IF(RIGHT(B1:B10,1)="v",--LEFT(B1:B10,LEN(B1:B10)-1)))} Check it out and let me know how it goes Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
Pinmaster, I tried it several times and my formula appears just as you have it listed. Now I'm annoyed because I know this CAN work - I just don't know how to MAKE it work! Any other thoughts as to why its not working??? -- Donna123 ------------------------------------------------------------------------ Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
Thank You Pinmaster!! Originally I had tried the formula several times and it would not work!! After my last post I decided to try it one last time and IT WORKED!!! I'm sooooooo happpppy!!! Thank you Thank you Thank you!!!! -- Donna123 ------------------------------------------------------------------------ Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel as Attendance Tracker
Your quite welcome, glad I could help and thanks for the feedback! Good luck with your project! Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495940 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
| convert pocket excel back to standard excel | Excel Discussion (Misc queries) | |||
| Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
| document tracker using Excel not Access | Excel Discussion (Misc queries) | |||
| Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
Linear Mode
