• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Me, I'm Confused......

S

salgal8

New Member
C4 is where the total amount of day a person has for Company paid days.

C5 are the Days remaining from days taken. =C4-(COUNT(E3:I5))

E3:I5 are 15 cells that require you to input the date of the day that was taken off, it reads like this =COUNT(E3:I5)


However; I want to be able to take HOURS off, not days. like I want to take half day from work. Is there anyway I can change this made up template?
 
Salgal8


Can you post a sample file which shows us your problem

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi, salgal8!

Ok, you used 2shared service but you didn't post the link to your file, would you please do it? Otherwise people who might help you should perform some illegal and forbidden actions (like hacking your computer to find out which is the link, or kidnap you and ask for the link as ransom) in order to aid you.

:)

Regards!
 
Sorry, I'm new to this. Thank you ;-)


http://www.2shared.com/file/5ClcxQoL/Time_Off_Tracking_-_All_Compan.html
 
Hi, salgal8!

Yes, it's ok, link correctly posted and file downloadable.

Regards!


@all

Hi!

Related topic at:

http://chandoo.org/forums/topic/vacation-tracking

Regards!
 
Hi, salgal8!


The uploaded file does the job for whole days, as vacations are usually taken as full days.

You could transform it to be hourly based to take half days or hours as you asked, but you'll have to resolve these issues:

a) How many hours per day do people work?

b) All people work the same hours?

c) Which is the minimum hour no. allowed to take? Just 4 hs, e.g., or any no. of hours?

d) How are you going to input the data? 15 cell blocks aren't suitable any more.


At least these, maybe others arise after your comments.


Regards!
 
In answer to your questions:

a) We only get 8 hours a day.

b) The company only give 40 hours a year to our vacation fund. So one week, 8x5=40 hours.

c) Min = 1 hour to max = 8 hours.

d) Good questions! Could use more? What do you think?


How about I put another excel sheet up and see if you can help me figure out how to make a formula for that?


http://www.2shared.com/file/4XmDIA1N/2013-Employee-Attendance-Track.html
 
Hi, salga8!


It sounds weird that the company allow personnel to take vacations as 1h per day, for example, but it's your company and their rules so let us play with them.


The main issue is that you'll have to enter data with this attributes:

- Non-working type (vacation, personal, training, compassion)

- Non-working date

- Not worked hours


Nowadays you've made 4 groups of 15 cells, one for each type. But you have 1 of those 15 cells for entering the date, hence you should have 2 cells for entering date & hours, ergo you'll have 30 cells per group. The actual structure doesn't look like particularly efficient and user friendly for that purpose, it seems as a summary of the actual data which you could enter and store in a different worksheet.


The no. of employees and how you'd enter the detailed data and which additional handling of that data (filtering, summarizing by date, by sector, ...) should be considered for the worksheet layout.


Regards!
 
@SirJB7

Slight rabbit hole, but taking 1 hr is handy for parents who need to leave suddenly to pick up kids from school, or if you have a doctor appt or something.
 
@Luke M

Hi!

Yes, I know that and I've done, do and will do that whenever I needed (and I could and "they" let me).

Not now (actually never in my case) but I had colleagues to whom this happened, that this extra hours taken usually for personal motives (doc visits, school kids's meetings, ...) were discounted at the end of the month or period when getting paid the salary if they didn't compensate the non-worked time.

But vacation or holiday days can't be taken in hours breaking a day into a few pieces, and it's forbidden by working laws or union settlements, not only in my country but in my region as far as I know. One thing is the annual or periodic vacation days and other the permission, formalities or compassion or whatsoever little periods (from hours to a couple of days) that an employee could take.

Regards!
 
My company only gives out Vacation time no other type of perks, no sick time. It's all included as Vacations, so if your sick take a Vacation day. So how would you work this in:

sumif or hour>24 ??
 
Hi, salgal8!

If you enter the data in 2 separate cells for each day (date in one cell, hours no. in other) you could use SUM or SUMIF of the proper cell range that include the 2nd groupo of cells and compare it against the product of the days available or maximum for that type by the no. of hours a day (8).

If you enter the data in 2 separate cells for each period (date & time from in one cell, and date & time to in another cell) then you'll have to work with whole days multiplied by 8 plus the partial starting and ending dates if applicable.

Once again, without the data layout it's not easy to make suitable specific suggestions.

Regards!
 
Does this help? LOL '-)

2012

Elvis Anton Vacation

Date Hired =Sheet2!D2

Authorized 5

Remaining =C4-(COUNT(E3:I5))

Used: =COUNT(E3:I5)

Ashley Baxendale Vacation

Date Hired =Sheet2!D3

Authorized 5

Remaining =C9-(COUNT(E8:I10))

Used: =COUNT(E8:I10)
 
Hi, salgal8!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Help%20Me%2C%20I%27m%20Confused......%20-%20Time%20Off%20Tracking%20-%20All%20Company%20%28for%20salgal8%20at%20chandoo.org%29.xlsx


It has 3 worksheets:

Employees, with your former employee list of worksheet Sheet2 but standardized and in table format

Vacations, with the data related to each employee and absence, in table format too

Service, same as previous.


Worksheet Service:


a) Employee data

C2: =INDICE(EmployeeTable[Name];ENTERO((FILA()-1)/5)+1) -----> in english: =INDEX(EmployeeTable[Name],INT((ROW()-1)/5)+1)

C3: Idem but for [Date hired]

C4: Idem but for [Authorized] by 8 (hours)

C5: =C4-(SUMA(E3:I5)) -----> in english: =C4-(SUM(E3:I5))

... repeated in C7:C10, C12:C15, ...


b) Absence type group (array formula)


E2:I5 : =SI.ERROR(INDICE(VacationTable;COINCIDIR(INDICE(EmployeeTable[Name];ENTERO((FILA()-1)/5)+1)&INDICE($2:$2;1;ENTERO((COLUMNA()-4+5)/6)*6-1)&(RESIDUO(RESIDUO(FILA()-1;5);5)-2)*5+RESIDUO(COLUMNA()-4;6);VacationTable[Employee Name]&VacationTable[Absence type]&VacationTable[Order];0);6);"") -----> in english: =IFERROR(INDEX(VacationTable,MATCH(INDEX(EmployeeTable[Name],INT((ROW()-1)/5)+1)&INDEX($2:$2,1,INT((COLUMN()-4+5)/6)*6-1)&(MOD(MOD(ROW()-1,5),5)-2)*5+MOD(COLUMN()-4,6),VacationTable[Employee Name]&VacationTable[Absence type]&VacationTable[Order],0),6),"")

... repeated in every group across & down.

Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.

In summary this little formula searches in the vacation table using the arguments of: employee name: INDEX(EmployeeTable[Name],INT((ROW()-1)/5)+1)

absence type: INDEX($2:$2,1,INT((COLUMN()-4+5)/6)*6-1)

ocurrence order: (MOD(MOD(ROW()-1,5),5)-2)*5+MOD(COLUMN()-4,6)

into the range created by the combination of name, absence type & order of VacationTable table.


I6 Used or Balance: =SUMA(E3:I5) -----> in english: =SUM(E3:I5) (instead of COUNT)


This will do the job for counting hours entered in worksheet Vacations instead of days (previously entered in same worksheet Service). But I doesn't reflect the date where the hours are taken. If you need so, the whole columns E:AG should be duplicated aside or in other worksheet so as to have an area for counting hours (actual E:AG, then new area) and a show the date or date & hours in actual E:AG area.


Just advise if any issue.


Regards!
 
Usted es un DIOS de Excel ..... Te amo! Gracias


In english: You are a GOD of Excel.....I love you! Thank you, Thank You '-)
 
Hi, salgal8!


Muchas gracias, pero aún queda por ver si es necesario tener las fechas en el resumen (creo que sí).


Thank you very much, but still pending to see if dates are required in the summary (I think so).


Regards!


PS: BTW, male of female? :p ...just to know how to respond to being loved :)
 
People do not express love for SirJB7 (male or female)they send tins of carlsberg :) It is an unwritten law of Chandoo forums
 
@b(ut)ob(ut)hc

Hi, my old friendly dog!

Good afternoon.

I felt as if the job wasn't finished, so I'm thinking about the most user friendly way to display data from 2 cells in 1... still thinking, with a glass of Carlsberg nearby.

Regards!
 
@aakashjain153

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!

PS: Please don't answer here at this thread.
 
Hi, salgal8!


Download again the updated file from same previous link.


I renamed worksheet Service as ServiceCalculations and copied it as Service again to hold the date and no. of hours of each non-worked occurrence in the 15-entry groups, so as to keep your original names: Service for the summary data indication date & hours, and ServiceCalculation for performing hour calculations (helper worksheet).


The new formula for the matrix is:

E2:I5 : =SI.ERROR(TEXTO(INDICE(VacationTable;COINCIDIR(INDICE(EmployeeTable[Name];ENTERO((FILA()-1)/5)+1)&INDICE($2:$2;1;ENTERO((COLUMNA()-4+5)/6)*6-1)&(RESIDUO(RESIDUO(FILA()-1;5);5)-2)*5+RESIDUO(COLUMNA()-4;6);VacationTable[Employee Name]&VacationTable[Absence type]&VacationTable[Order];0);5);"mm/dd")&" "&INDICE(VacationTable;COINCIDIR(INDICE(EmployeeTable[Name];ENTERO((FILA()-1)/5)+1)&INDICE($2:$2;1;ENTERO((COLUMNA()-4+5)/6)*6-1)&(RESIDUO(RESIDUO(FILA()-1;5);5)-2)*5+RESIDUO(COLUMNA()-4;6);VacationTable[Employee Name]&VacationTable[Absence type]&VacationTable[Order];0);6)&"h";"") -----> in english: =IFERROR(TEXT(INDEX(VacationTable,MATCH(INDEX(EmployeeTable[Name],INT((ROW()-1)/5)+1)&INDEX($2:$2,1,INT((COLUMN()-4+5)/6)*6-1)&(MOD(MOD(ROW()-1,5),5)-2)*5+MOD(COLUMN()-4,6),VacationTable[Employee Name]&VacationTable[Absence type]&VacationTable[Order],0),5),"mm/dd")&" "&INDEX(VacationTable,MATCH(INDEX(EmployeeTable[Name],INT((ROW()-1)/5)+1)&INDEX($2:$2,1,INT((COLUMN()-4+5)/6)*6-1)&(MOD(MOD(ROW()-1,5),5)-2)*5+MOD(COLUMN()-4,6),VacationTable[Employee Name]&VacationTable[Absence type]&VacationTable[Order],0),6)&"h","")

which before was the extract of the 6th column (hours) of table VacationTable and now is just a concatenation of the 5th column (date) formated "mm/yy" and the same 6th column (it's easier to explain in this way rather than analyzing that monster).


Also changed all dates from my natural dd/mm/yyyy to your mm/dd/yyyy regional config, I guess.


Give a look to it, play a little or a lot with it and tell me if it's now Ok, which I

hope.


Just advise if any issue.


Regards!


PS: BTW, you neither answered my last previous question nor followed my friend b(ut)ob(ut)hc's indications :)
 
Hello Sir JB7, Got your message, and again I'm grateful, still trying to grasp the meaning I'm still learning this wonderful software, so much to learn. Sometimes it make my head swim. This new formula looks great too! I thank you again for taking the time to work on this and teach this old dog some new trick! LOL.


By the way I'm an "vieja", got to put the emphasis on older woman, but you at heart. LOL!


Now if I could just figure out how to post my photo on this site, I would. I would like to keep in contact with you because I now consider you the GURU of Excel to me, is it customary to give out my email address for future contact?
 
You must log in or register to reply here.
Back
Top