Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
I,m making a timesheet for regular worktime, 50% overtime hour and 100%.
regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 I hope someone can help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
I,m making a timesheet for regular worktime, 50% overtime hour and 100%.
regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 Not sure what HVIS is (probably language specific IF statement). Anyway, assuming column H is calculated (in case a person works less than 7 and a half hours), and assuming column J values calculate correctly, can't you set column I to 3 if column J is greater than 0 and to the difference between column K and column H otherwise? I am thinking something like this (using IF statements where the decimal separator is the dot and argument separators are the comma)... I13: =IF(J130,3,IF(K13=7.5,K13-H13,0)) Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
If G13-F13 is lunch that should be deducted you shouldn't add it
the first 7.5 can be written =MIN(7,5;((E13-D13-(G13-F13))*24)) between 7.5 and 10.5 can be written =STØRST(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) and all hours worked greater than 10.5 =STØRST(0;((E13-D13-(G13-F13))*24)-10,5) -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... I,m making a timesheet for regular worktime, 50% overtime hour and 100%. regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 I hope someone can help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
Hi, Rick
I see you use the difference between K13 and H13, the problem is that K13 is the SUM of H13, I13 and J13 =SUMMER(H13:J13) inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 06:00 12:00 13:00 16:00 7,5 ??? 0,0 ??? (1,5) (9,0) I need: H13 to be between 0,0 to 7,5 (this one works!) I13 to be between 0,0 to 3,0 (this is the hours after H13 (7,5), not working?) J13 to be between 0,0 to 13,5 (this is the hours after H13 and I13 (10,5), works!) I13: =HVIS(((E13-D13)+(G13-F13))*247,5;3;((E13-D13)+(G13-F13))*24-7,5) But the value only shows negative numbers and 3??? NB! I think the HVIS comand is the same as IF, logic test. Harald Systemgulv Rick Rothstein (MVP - VB) skrev: I,m making a timesheet for regular worktime, 50% overtime hour and 100%. regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 Not sure what HVIS is (probably language specific IF statement). Anyway, assuming column H is calculated (in case a person works less than 7 and a half hours), and assuming column J values calculate correctly, can't you set column I to 3 if column J is greater than 0 and to the difference between column K and column H otherwise? I am thinking something like this (using IF statements where the decimal separator is the dot and argument separators are the comma)... I13: =IF(J130,3,IF(K13=7.5,K13-H13,0)) Rick |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
Hi, Peo Sjoblom
G13-F13 is the hours after lunch. I've tried what you suggested, but gets the error #NAVN? (#NAME?) when I use the command STØRST. I,ve tried to use the command MAKS instead, but the result is wrong? =MAKS(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 06:00 12:00 13:00 16:00 7,5 ??? 0,0 ??? (1,5) (9,0) Harald Systemgulv Peo Sjoblom skrev: If G13-F13 is lunch that should be deducted you shouldn't add it the first 7.5 can be written =MIN(7,5;((E13-D13-(G13-F13))*24)) between 7.5 and 10.5 can be written =STØRST(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) and all hours worked greater than 10.5 =STØRST(0;((E13-D13-(G13-F13))*24)-10,5) -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... I,m making a timesheet for regular worktime, 50% overtime hour and 100%. regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 I hope someone can help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
OK, I misunderstood, so what you are saying is that basically lunch time is
F13-E13 so let's do it again The first would be =MIN(7,5;((G13-D13-(F13-E13))*24)) formatted as general (not time) second would be =MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) and third =MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) to test it I did as follows D13 E13 F13 G13 08:00 12:00 13:00 21:30 using the formulas I provide will return 7.5 regular, 3.0 50% and 2.0 100% change the end time in G13 to 18:30 we will get 7.5 regular, 2.0 50% and 0 100%, change end time to 16:15 we will get 7.25 regular and zero 50 and 100% It is important that you use general (or the Norwegian equivalent) or number formatting and NOT time formatting since it will be way off if you do. For instance after you multiply a time value with 24 you will get the decimal equivalent and 7,5 is the same as 7,5 days which is 180 hours Sorry about the wrong function translation, I just assumed you used something similar to Swedish function names. Also if you get any error message while applying the formula make sure the delimiters are correct, I am located in the US and at least Swedish delimiters are semicolons as opposed to commas and the decimals are written 7,5 as opposed to US 7.5 -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... Hi, Peo Sjoblom G13-F13 is the hours after lunch. I've tried what you suggested, but gets the error #NAVN? (#NAME?) when I use the command STØRST. I,ve tried to use the command MAKS instead, but the result is wrong? =MAKS(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 06:00 12:00 13:00 16:00 7,5 ??? 0,0 ??? (1,5) (9,0) Harald Systemgulv Peo Sjoblom skrev: If G13-F13 is lunch that should be deducted you shouldn't add it the first 7.5 can be written =MIN(7,5;((E13-D13-(G13-F13))*24)) between 7.5 and 10.5 can be written =STØRST(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) and all hours worked greater than 10.5 =STØRST(0;((E13-D13-(G13-F13))*24)-10,5) -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... I,m making a timesheet for regular worktime, 50% overtime hour and 100%. regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 I hope someone can help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
Oops! Change MAX to MAKS for the last 2 formulas
-- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... OK, I misunderstood, so what you are saying is that basically lunch time is F13-E13 so let's do it again The first would be =MIN(7,5;((G13-D13-(F13-E13))*24)) formatted as general (not time) second would be =MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) and third =MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) to test it I did as follows D13 E13 F13 G13 08:00 12:00 13:00 21:30 using the formulas I provide will return 7.5 regular, 3.0 50% and 2.0 100% change the end time in G13 to 18:30 we will get 7.5 regular, 2.0 50% and 0 100%, change end time to 16:15 we will get 7.25 regular and zero 50 and 100% It is important that you use general (or the Norwegian equivalent) or number formatting and NOT time formatting since it will be way off if you do. For instance after you multiply a time value with 24 you will get the decimal equivalent and 7,5 is the same as 7,5 days which is 180 hours Sorry about the wrong function translation, I just assumed you used something similar to Swedish function names. Also if you get any error message while applying the formula make sure the delimiters are correct, I am located in the US and at least Swedish delimiters are semicolons as opposed to commas and the decimals are written 7,5 as opposed to US 7.5 -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... Hi, Peo Sjoblom G13-F13 is the hours after lunch. I've tried what you suggested, but gets the error #NAVN? (#NAME?) when I use the command STØRST. I,ve tried to use the command MAKS instead, but the result is wrong? =MAKS(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 06:00 12:00 13:00 16:00 7,5 ??? 0,0 ??? (1,5) (9,0) Harald Systemgulv Peo Sjoblom skrev: If G13-F13 is lunch that should be deducted you shouldn't add it the first 7.5 can be written =MIN(7,5;((E13-D13-(G13-F13))*24)) between 7.5 and 10.5 can be written =STØRST(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) and all hours worked greater than 10.5 =STØRST(0;((E13-D13-(G13-F13))*24)-10,5) -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... I,m making a timesheet for regular worktime, 50% overtime hour and 100%. regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 I hope someone can help! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
YES, you solved my problem Peo Sjoblom!
I wil use your formula for I13, with some smal changes as you suggested. Instead of MAX I used MAKS. =MAKS(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) Thanks Harald Systemgulv NB! Your second and third formula is the same... but it was just what I needed:) Peo Sjoblom skrev: OK, I misunderstood, so what you are saying is that basically lunch time is F13-E13 so let's do it again The first would be =MIN(7,5;((G13-D13-(F13-E13))*24)) formatted as general (not time) second would be =MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) and third =MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) to test it I did as follows D13 E13 F13 G13 08:00 12:00 13:00 21:30 using the formulas I provide will return 7.5 regular, 3.0 50% and 2.0 100% change the end time in G13 to 18:30 we will get 7.5 regular, 2.0 50% and 0 100%, change end time to 16:15 we will get 7.25 regular and zero 50 and 100% It is important that you use general (or the Norwegian equivalent) or number formatting and NOT time formatting since it will be way off if you do. For instance after you multiply a time value with 24 you will get the decimal equivalent and 7,5 is the same as 7,5 days which is 180 hours Sorry about the wrong function translation, I just assumed you used something similar to Swedish function names. Also if you get any error message while applying the formula make sure the delimiters are correct, I am located in the US and at least Swedish delimiters are semicolons as opposed to commas and the decimals are written 7,5 as opposed to US 7.5 -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... Hi, Peo Sjoblom G13-F13 is the hours after lunch. I've tried what you suggested, but gets the error #NAVN? (#NAME?) when I use the command STØRST. I,ve tried to use the command MAKS instead, but the result is wrong? =MAKS(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 06:00 12:00 13:00 16:00 7,5 ??? 0,0 ??? (1,5) (9,0) Harald Systemgulv Peo Sjoblom skrev: If G13-F13 is lunch that should be deducted you shouldn't add it the first 7.5 can be written =MIN(7,5;((E13-D13-(G13-F13))*24)) between 7.5 and 10.5 can be written =STØRST(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5) and all hours worked greater than 10.5 =STØRST(0;((E13-D13-(G13-F13))*24)-10,5) -- M.v.h. Peo Sjoblom "Systemgulv" wrote in message ... I,m making a timesheet for regular worktime, 50% overtime hour and 100%. regular worktime is 7,5 hour For this I use: =HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24) NB! HVIS=IF, I think? I,m norwegian. All hours after 7,5 is overtime, 3 hour 50% and the rest is 100% I,ve figured out the 100% with this: HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0) All hours after 10,5 is 100% My problem is how to get the 3 hour 50% to count. It looks like this: inn out inn out reg 50% 100% Total D13 E13 F13 G13 H13 I13 J13 K13 09:00 12:00 13:00 22:00 7,5 ??? 1,5 12 I hope someone can help! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with my timesheet? regular, 50% and 100%
"Systemgulv" wrote in message ... YES, you solved my problem Peo Sjoblom! I wil use your formula for I13, with some smal changes as you suggested. Instead of MAX I used MAKS. =MAKS(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5) Thanks Harald Systemgulv NB! Your second and third formula is the same... but it was just what I needed:) Oops! Not my day today Peo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
regular and overtime hours | Excel Worksheet Functions | |||
Regular Expression | Excel Discussion (Misc queries) | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
Log and regular plot | Excel Discussion (Misc queries) | |||
ALL CAPS to regular text? | Excel Worksheet Functions |