Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick, tota
I downloaded a timesheet like the one below. I added the extra in/out and
can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick, tota
Assuming the mismatch between your formula and rows is typo (row 12 contains
headings), the formula works OK for me. I have the time columns formatted as hh:mm and Reg Hours as General OT hours (formatted as General): =MAX(0,(($D12-$C12)+($F12-$E12)+($H12-$G12))*24-8) What error do you get? HTH "Kathryn Pundt" wrote: I downloaded a timesheet like the one below. I added the extra in/out and can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick,
Toppers:
You have saved me hours. Thank you! Thank you!! Kathryn "Toppers" wrote: Assuming the mismatch between your formula and rows is typo (row 12 contains headings), the formula works OK for me. I have the time columns formatted as hh:mm and Reg Hours as General OT hours (formatted as General): =MAX(0,(($D12-$C12)+($F12-$E12)+($H12-$G12))*24-8) What error do you get? HTH "Kathryn Pundt" wrote: I downloaded a timesheet like the one below. I added the extra in/out and can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick,
The timesheet is perfect, except for the fact that now our accounting
department wants the times to be AM/PM, not military. Can we still calculate the total times and convert the time to AM/PM to comply with the "heads"? Example: in 8:00 AM / out 12:00 PM / in 1:00 PM, etc. "Toppers" wrote: Assuming the mismatch between your formula and rows is typo (row 12 contains headings), the formula works OK for me. I have the time columns formatted as hh:mm and Reg Hours as General OT hours (formatted as General): =MAX(0,(($D12-$C12)+($F12-$E12)+($H12-$G12))*24-8) What error do you get? HTH "Kathryn Pundt" wrote: I downloaded a timesheet like the one below. I added the extra in/out and can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick,
OT hours as AM/PM? Frankly that doesn't make any sense at all, what if you
have 20 hours of OT, are you going to use 08:00 PM? If you mean what you put into the cells to be calculated like IN Out 08:00 AM 11:30 AM and so on, that is just a formatting issue. Just change the format -- Regards, Peo Sjoblom "Kathryn Pundt" wrote in message ... The timesheet is perfect, except for the fact that now our accounting department wants the times to be AM/PM, not military. Can we still calculate the total times and convert the time to AM/PM to comply with the "heads"? Example: in 8:00 AM / out 12:00 PM / in 1:00 PM, etc. "Toppers" wrote: Assuming the mismatch between your formula and rows is typo (row 12 contains headings), the formula works OK for me. I have the time columns formatted as hh:mm and Reg Hours as General OT hours (formatted as General): =MAX(0,(($D12-$C12)+($F12-$E12)+($H12-$G12))*24-8) What error do you get? HTH "Kathryn Pundt" wrote: I downloaded a timesheet like the one below. I added the extra in/out and can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick,
Peo,
The timesheet has the time entered as military (see below email string), then calculates and converts to 8 in total reg hrs and # for any overtime. It is the miliary time that is the problem. The way I understand it, to be able to calculate time, it first must be military (my sheet lets you enter reg clock time, then converts it to military). What I need it to is not show it as military time in the timesheet itself, but to calculate it in military time. If you can think of a better way or just another way to get what will satisfy "the heads", please let me know. I worked on this all weekend and finally gave in and got online and posted my question. What I thought was going to be easy, turned out to be not so easy for me. "Peo Sjoblom" wrote: OT hours as AM/PM? Frankly that doesn't make any sense at all, what if you have 20 hours of OT, are you going to use 08:00 PM? If you mean what you put into the cells to be calculated like IN Out 08:00 AM 11:30 AM and so on, that is just a formatting issue. Just change the format -- Regards, Peo Sjoblom "Kathryn Pundt" wrote in message ... The timesheet is perfect, except for the fact that now our accounting department wants the times to be AM/PM, not military. Can we still calculate the total times and convert the time to AM/PM to comply with the "heads"? Example: in 8:00 AM / out 12:00 PM / in 1:00 PM, etc. "Toppers" wrote: Assuming the mismatch between your formula and rows is typo (row 12 contains headings), the formula works OK for me. I have the time columns formatted as hh:mm and Reg Hours as General OT hours (formatted as General): =MAX(0,(($D12-$C12)+($F12-$E12)+($H12-$G12))*24-8) What error do you get? HTH "Kathryn Pundt" wrote: I downloaded a timesheet like the one below. I added the extra in/out and can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick,
Topper's formula deals with regular Excel time, the *24 converts it to
decimal time, to me military time is 800 for 08:00 AM and 2200 for 10:00 PM is that what you are using? -- Regards, Peo Sjoblom "Kathryn Pundt" wrote in message ... Peo, The timesheet has the time entered as military (see below email string), then calculates and converts to 8 in total reg hrs and # for any overtime. It is the miliary time that is the problem. The way I understand it, to be able to calculate time, it first must be military (my sheet lets you enter reg clock time, then converts it to military). What I need it to is not show it as military time in the timesheet itself, but to calculate it in military time. If you can think of a better way or just another way to get what will satisfy "the heads", please let me know. I worked on this all weekend and finally gave in and got online and posted my question. What I thought was going to be easy, turned out to be not so easy for me. "Peo Sjoblom" wrote: OT hours as AM/PM? Frankly that doesn't make any sense at all, what if you have 20 hours of OT, are you going to use 08:00 PM? If you mean what you put into the cells to be calculated like IN Out 08:00 AM 11:30 AM and so on, that is just a formatting issue. Just change the format -- Regards, Peo Sjoblom "Kathryn Pundt" wrote in message ... The timesheet is perfect, except for the fact that now our accounting department wants the times to be AM/PM, not military. Can we still calculate the total times and convert the time to AM/PM to comply with the "heads"? Example: in 8:00 AM / out 12:00 PM / in 1:00 PM, etc. "Toppers" wrote: Assuming the mismatch between your formula and rows is typo (row 12 contains headings), the formula works OK for me. I have the time columns formatted as hh:mm and Reg Hours as General OT hours (formatted as General): =MAX(0,(($D12-$C12)+($F12-$E12)+($H12-$G12))*24-8) What error do you get? HTH "Kathryn Pundt" wrote: I downloaded a timesheet like the one below. I added the extra in/out and can't seem to get the formulas to work. I used the existing formula and just added the two new columns. =IF((((D12-C12)+(F12-E12)+(H12-G12))*24)8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24)) B C D E F G H I J K L M N 12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total 13 Sunday 0.00 14 Monday 0.00 15 Tuesday 0.00 16 Wednesday 0.00 17 Thursday 0.00 18 Friday 0.00 19 Saturday 0.00 20 21 Total 0.00 0.00 0.00 0.00 0.00 0.00 Can someone help me figure out what I am doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pie Chart Sectional Pull-outs | Charts and Charting in Excel | |||
how do I add words, holiday =1, holiday am=0.5 | Excel Worksheet Functions | |||
Print outs from Excel has symbols instead of words | Excel Discussion (Misc queries) | |||
How do i copy numbers from Web and paste to excel and and get tota | Excel Worksheet Functions | |||
Holiday Planner show holiday taken? | Excel Worksheet Functions |