|
|
| Author |
Message |
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
 Posted: 07/15/03 - 14:05 Post subject: Microsoft Excel help
|
|
|
Ya, I know, its probably really really easy to configure, but I've never used Excel.
Where can I go to find out how to set up Excel so that I can type in the time I am supposed to start work, supposed to get off and the supposed-to-be pay, as well as the actual time I clock in and out, and the actual amount of money I made?
I know its possible, and that its easy, I just dont know how to configure it.
|
|
|
Back to top
|
|
|
|
 |
Zuldane
RealPoor Guru
Joined: 11 Oct 2002
Posts: 4057
Location: At sea.
|
|
Posted: 07/15/03 - 14:15 Post subject:
|
|
|
|
IT CAN'T BE DONE EVER
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/15/03 - 14:17 Post subject:
|
|
|
OMG U R TEH SUQ HEPL ME !!!1!!111!!!111
plz?
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/15/03 - 16:03 Post subject:
|
|
|
Basic Excel functionality
First you have to understand how excel works to get to what you want it to do.
Consider each cell has it's own function and that function is to represent values you tell it you want, be that thru a formula leading to that cell or a value you actually input.
Typically the information is linear which is why rows and columns become helpful later.
so you want to know 2 values (what you are paid) based off of 4 a number of hours (2 additional values).
I'll give you a really basic form you can use, follow the cells in the number/letter combo:
A-------- B-------- C-------- D-------- E----------
1-Hours- -Actual- --$/hr-- --Total-- --Actual total---
2 8 4.2 10 =a2*c2 =b2*c2
In "a" 1 thru whatever you will put what your normal workday is or should be, (8 hrs?) and stretch it straight down the column.
In "b" 1 thru whatever you will put what your actual workday is (7.5, 4.2, 2, whatever the number of hrs you worked is)
In “c” you put your hr rate (10) and stretch it straight down the column.
For your ideal total:
Now in D and E you will need to manipulate the date that will give you your final output wich is the amount you ideally will be paid and the amount you actually get paid. So in D (your idea total) you want to multiply “a” by “c” so to do that you need to put the following formula in “D”:
=a2*c2
Which, logically, if you read it states I want to multiply the value in the A with the value in C. The reason the number 2 is after “a” and “c” is that you are talking about that line (2). You didn’t start on a1 because the first line is your header.
For your actual total:
Much like the first formula you just adjust what value you are changing (the number of actual hours). That would be expressed in the formula:
=b2*c2
After you understand that functionality all other values that you could ever want are just a matter of developing formulas that will interact with the established values in your spreadsheet.
|
|
|
Back to top
|
|
|
|
|
Zuldane
RealPoor Guru
Joined: 11 Oct 2002
Posts: 4057
Location: At sea.
|
|
Posted: 07/15/03 - 16:06 Post subject:
|
|
|
|
You can hack the gibson with Excel and program SQL servers
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/15/03 - 16:08 Post subject:
|
|
|
HACK THE PLANET!!!
THEY ARE TRASHING OUR RIGHTS!!!!
I'd love to get a slice off that gibson.
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/16/03 - 02:29 Post subject:
|
|
|
| Banzai wrote: | Basic Excel functionality
First you have to understand how excel works to get to what you want it to do.
Consider each cell has it's own function and that function is to represent values you tell it you want, be that thru a formula leading to that cell or a value you actually input.
Typically the information is linear which is why rows and columns become helpful later.
so you want to know 2 values (what you are paid) based off of 4 a number of hours (2 additional values).
I'll give you a really basic form you can use, follow the cells in the number/letter combo:
A-------- B-------- C-------- D-------- E----------
1-Hours- -Actual- --$/hr-- --Total-- --Actual total---
2 8 4.2 10 =a2*c2 =b2*c2
In "a" 1 thru whatever you will put what your normal workday is or should be, (8 hrs?) and stretch it straight down the column.
In "b" 1 thru whatever you will put what your actual workday is (7.5, 4.2, 2, whatever the number of hrs you worked is)
In “c” you put your hr rate (10) and stretch it straight down the column.
For your ideal total:
Now in D and E you will need to manipulate the date that will give you your final output wich is the amount you ideally will be paid and the amount you actually get paid. So in D (your idea total) you want to multiply “a” by “c” so to do that you need to put the following formula in “D”:
=a2*c2
Which, logically, if you read it states I want to multiply the value in the A with the value in C. The reason the number 2 is after “a” and “c” is that you are talking about that line (2). You didn’t start on a1 because the first line is your header.
For your actual total:
Much like the first formula you just adjust what value you are changing (the number of actual hours). That would be expressed in the formula:
=b2*c2
After you understand that functionality all other values that you could ever want are just a matter of developing formulas that will interact with the established values in your spreadsheet. |
That is pretty close to what Im looking for; but is there a way that, instead of me having to do the math and figure out the decimal form of the hours I work, I can type in a clock in / clock out time ( say in military time ) and the system will total it for me?
Also, every day I start/end at different times, and I dont work every day..
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/16/03 - 10:40 Post subject:
|
|
|
Short answer is no, you will have to do math one way or another to figure out the values you are after.
You can right click the cell and use the "format cell" feature to tell the sheet that you are dealing with "time".
Once you establish time you have to make a formula that gives a number value based off the amount of time that you can multiply by the number of your pay rate. I'm sure this formula is pretty common, you just have to find it on the net someplace. Check tutorials and google if you are dead set on that type of accounting. I'm willing to bet that the help tutorials within the excel program could even tell you.
Makeing number values for time isn't difficult though, most workplaces only account by 15 min incriments so:
15 min = .25
30 min = .50
45 min = .75
1 hr = 1.00
|
|
|
Back to top
|
|
|
|
|
Annonymous
Total Newbie
Joined: 17 Oct 2002
Posts: 40
|
|
Posted: 07/16/03 - 20:01 Post subject:
|
|
|
| Banzai wrote: | | Short answer is no, you will have to do math one way or another to figure out the values you are after. |
or you could just have Excel calculate the values for you (afterall, that's what it's designed to do)..
| Code: |
A B C D E
1 Start Time End Time Hours Rate Total
2 9:00 AM 5:00 PM 8.00 $20.00 $160.00
|
A2 is formatted as Time
B2 is formatted as Time
C2 is formatted as Number
D2 is formatted as Currency
E2 is formatted as Currency
Enter your start time in A2 (as 12 or 24-hour time)
Enter your finish time in B2 (as 12 or 24-hour time)
Enter the following formula in C2 "=(B2-A2)*24"
Enter your hourly rate in D2
Enter the following formula in E2 "=C2*D2"
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/16/03 - 22:59 Post subject:
|
|
|
| Annonymous wrote: | | Banzai wrote: | | Short answer is no, you will have to do math one way or another to figure out the values you are after. |
or you could just have Excel calculate the values for you (afterall, that's what it's designed to do)..
| Code: |
A B C D E
1 Start Time End Time Hours Rate Total
2 9:00 AM 5:00 PM 8.00 $20.00 $160.00
|
A2 is formatted as Time
B2 is formatted as Time
C2 is formatted as Number
D2 is formatted as Currency
E2 is formatted as Currency
Enter your start time in A2 (as 12 or 24-hour time)
Enter your finish time in B2 (as 12 or 24-hour time)
Enter the following formula in C2 "=(B2-A2)*24"
Enter your hourly rate in D2
Enter the following formula in E2 "=C2*D2" |
Very nice my friend, very nice
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/17/03 - 01:13 Post subject:
|
|
|
Ok, question...
When Im totaling up the hours and using the x-x form inorder to do so, how do you type it in when the smaller number is coming first?
Im not sure how to explain it..
| Code: |
H I J K
Actual Start Time Actual End Time Rate Total Hours
11 5:30 PM 1:18 AM $6.25
12 5:30 PM 12:15 AM $6.25
|
Try and work with that..
The problem seems to come up when I type in a shift that goes past midnight; therefor 'resetting' the clock, kind of. I keep screwing with the formula and just can't get it to work correctly.
To get the total hours for 11, I have tried the following formulas with their respective results.
=(I11-H11)*24 ====== -16.20
=(I11-H11)*12 ====== -8.10
=(H11-I11)*24 ====== 16.20
=(H11-I11)*12 ====== 8.10
=I11-H11 ====== -0.68
=H11-I11 ====== 0.68
See what I'm saying?
|
|
|
Back to top
|
|
|
|
|
Alton
Rookie
Joined: 11 Oct 2002
Posts: 88
|
|
Posted: 07/17/03 - 09:10 Post subject:
|
|
|
You'll probably need to use the 'time' format that also includes the date.
I bet it would calculate the time correctly then.
-alton
|
|
|
Back to top
|
|
|
|
|
Manuva
Banned
Joined: 12 Oct 2002
Posts: 2536
|
|
Posted: 07/17/03 - 09:23 Post subject:
|
|
|
|
I have to poop
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/17/03 - 09:57 Post subject:
|
|
|
| Alton wrote: | You'll probably need to use the 'time' format that also includes the date.
I bet it would calculate the time correctly then.
-alton |
yep
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/17/03 - 12:35 Post subject:
|
|
|
Bah, I have a nice format going with the date in the A colum; it makes it a lot cleaner then having the date next to every clock in/out time.
Any other way to do that? Or to just hide the date?
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/17/03 - 12:53 Post subject:
|
|
|
| Mental_Hernia wrote: | Bah, I have a nice format going with the date in the A colum; it makes it a lot cleaner then having the date next to every clock in/out time.
Any other way to do that? Or to just hide the date? |
You can hide any function you do not wish to see, right click the colum or row and select "hide" or just put the function in a diffrent place out of the way of the format you are building. Just be sure if you move the fields around that your formula is still pointing to the right cells =)
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/17/03 - 14:08 Post subject:
|
|
|
| Banzai wrote: | | Mental_Hernia wrote: | Bah, I have a nice format going with the date in the A colum; it makes it a lot cleaner then having the date next to every clock in/out time.
Any other way to do that? Or to just hide the date? |
You can hide any function you do not wish to see, right click the colum or row and select "hide" or just put the function in a diffrent place out of the way of the format you are building. Just be sure if you move the fields around that your formula is still pointing to the right cells =) |
I... can't say I understand..
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/17/03 - 15:46 Post subject:
|
|
|
what part?
The hide: select the rows or colums you don't want to see by clicking the number or letter in the header, then right click, then select "hide" if you want to see it agaqin select the 2 colums or rows that are on both sides of the hiden one, right click and select "unhide".
Moveing the function: put your formula for the date/time to a number of hrs. at the end of the sheet and just make sure your cells for the nice format you like point to it correctly.
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/17/03 - 16:31 Post subject:
|
|
|
| Banzai wrote: | what part?
The hide: select the rows or colums you don't want to see by clicking the number or letter in the header, then right click, then select "hide" if you want to see it agaqin select the 2 colums or rows that are on both sides of the hiden one, right click and select "unhide".
Moveing the function: put your formula for the date/time to a number of hrs. at the end of the sheet and just make sure your cells for the nice format you like point to it correctly. |
Oh ok, I see what you're saying..
I dont want to hide the whole colum; just the part of the box that says the date..
So if a box said this, for example: 7/7/03 5:43 PM
I want to be able to hide the date part..
Comprende?
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/17/03 - 21:58 Post subject:
|
|
|
Hum, you could right justify the cell and just cut off the left side =)
Or make the function of time be somewhere else on the sheet, put the time you like in the sheet itself inside where you want it then just point the real formula to the area you designated for the function. Basically it's a workaround for getting a look you are after without it being totally functional.
That make sense?
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/18/03 - 01:16 Post subject:
|
|
|
I just calculated it myself 
Thanks though, the rest helped a lot
|
|
|
Back to top
|
|
|
|
|
Annonymous
Total Newbie
Joined: 17 Oct 2002
Posts: 40
|
|
Posted: 07/18/03 - 01:31 Post subject:
|
|
|
Calculating the total hours over midnight is fairly easy as well. Going back to my first example, replace the formula in C2 with the following:
=IF((B2-A2)<0,B2-A2+1,B2-A2)*24
No dates required, just enter the times in regular 12 or 24-hour format...
|
|
|
Back to top
|
|
|
|
|
Banzai
Guest
|
|
Posted: 07/18/03 - 01:41 Post subject:
|
|
|
| Annonymous wrote: |
=IF((B2-A2)<0,B2-A2+1,B2-A2)*24
... |
Good stuff. Someone stayed awake in algebra. 
|
|
|
Back to top
|
|
|
|
|
Mental_Hernia
RealPoor Guru
Joined: 14 Oct 2002
Posts: 3336
Location: Texas
|
|
Posted: 07/18/03 - 18:27 Post subject:
|
|
|
|
Jeeesssusss.
|
|
|
Back to top
|
|
|
|
|
|
|
