Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max between one hour and second hour
In Out Hours 16:00 18:30 02:30 08:55 15:00 06:05 12:00 21:00 09:00 10:00 16:00 06:00 08:45 16:35 07:50 08:00 20:30 12:30 I have a hourlist like this, the hours are in format hh:mm, what I hope to manage is.. lets call the columns A B C D E IN OUT HOURS AFTER 18 AFTER 21 in column D i want to have all hours after 18:00 listet automaticly, so if a worker works to 19:30 the col. D should list 1:30 Is this possible with the HH:MM format?? I got this from a JE Mc Gimpsey Use =MAX(0, B2-0.75) Have two more questions : 1. Why the -0,75?? it works but could not understand why it works 2. IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and 21:00, and list only the hours between 18:00 and 21:00, and a new one from 21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00 between 18:00 and 21:00 the get 40% additional sallary and after 21:00 they get 100% more, would have bee SO nice to be able to make this calculation automatic -- Just a regular user |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max between one hour and second hour
0.75 = 06:00 PM or 18:00
1 equals one day equals 24 hours meaning that 1 hour equals 1/24 equals 0.041667, it also means that 12 hours equals 0.50 thus 18 hours equals 0.75 21:00 equals 0.875 written as a decimal so you can use the same technique -- Regards, Peo Sjoblom "Rockbear" wrote in message ... In Out Hours 16:00 18:30 02:30 08:55 15:00 06:05 12:00 21:00 09:00 10:00 16:00 06:00 08:45 16:35 07:50 08:00 20:30 12:30 I have a hourlist like this, the hours are in format hh:mm, what I hope to manage is.. lets call the columns A B C D E IN OUT HOURS AFTER 18 AFTER 21 in column D i want to have all hours after 18:00 listet automaticly, so if a worker works to 19:30 the col. D should list 1:30 Is this possible with the HH:MM format?? I got this from a JE Mc Gimpsey Use =MAX(0, B2-0.75) Have two more questions : 1. Why the -0,75?? it works but could not understand why it works 2. IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and 21:00, and list only the hours between 18:00 and 21:00, and a new one from 21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00 between 18:00 and 21:00 the get 40% additional sallary and after 21:00 they get 100% more, would have bee SO nice to be able to make this calculation automatic -- Just a regular user |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max between one hour and second hour
Excel stores dates and times as numbers.
For time calculations 24 hours is treated as 1 so 18:00 hours is 0.75, 21:00 would be 0.875 You can use the following to find the hours after 18:00 and before 21:00 if any =IF(AND(C1Time(18,0,0),C1<Time(21,0,0),Time(21,0, 0)-C1,"") Similarly for hours after 21:00 =IF(C1Time(21,0,0),C1-Time(21,0,0),"") Of course it is assumed that worker is out by midnight... Once you get the hours, you can multiply them by rate to get the total compensation. If you have daily rate then simply multiply the hours you get, if you have hourly rate then multiply the hours by rate and then by 24 "Rockbear" wrote: In Out Hours 16:00 18:30 02:30 08:55 15:00 06:05 12:00 21:00 09:00 10:00 16:00 06:00 08:45 16:35 07:50 08:00 20:30 12:30 I have a hourlist like this, the hours are in format hh:mm, what I hope to manage is.. lets call the columns A B C D E IN OUT HOURS AFTER 18 AFTER 21 in column D i want to have all hours after 18:00 listet automaticly, so if a worker works to 19:30 the col. D should list 1:30 Is this possible with the HH:MM format?? I got this from a JE Mc Gimpsey Use =MAX(0, B2-0.75) Have two more questions : 1. Why the -0,75?? it works but could not understand why it works 2. IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and 21:00, and list only the hours between 18:00 and 21:00, and a new one from 21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00 between 18:00 and 21:00 the get 40% additional sallary and after 21:00 they get 100% more, would have bee SO nice to be able to make this calculation automatic -- Just a regular user |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max between one hour and second hour
Excel stores time (and dates) as a number representing days. So 18 hours is
18/24 of a day, and 18/24 =0.75. You could use =MAX(B2-18/24) However, Excel would then give the answer 0.020833 since 30 mins is (1/2)/24 = 0.020833 of a day. Then you need to format the cell as time - Excel sometimes gets 'over helpful) If someone works to 19:30 you want 1:30 in D and 0 in E If they work to 22:45, you want 3 in D and 1:45 in E So in D use =MIN(3/24,MAX(0, B2-18/24)) (the 3/24 is the 3 hours from 18:00 to 21:00, we divide by 24 since time is stored as a fraction of a day) And in E use =MAX(0,B2-21/24) Hope this helps, please come back if you need more help best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rockbear" wrote in message ... In Out Hours 16:00 18:30 02:30 08:55 15:00 06:05 12:00 21:00 09:00 10:00 16:00 06:00 08:45 16:35 07:50 08:00 20:30 12:30 I have a hourlist like this, the hours are in format hh:mm, what I hope to manage is.. lets call the columns A B C D E IN OUT HOURS AFTER 18 AFTER 21 in column D i want to have all hours after 18:00 listet automaticly, so if a worker works to 19:30 the col. D should list 1:30 Is this possible with the HH:MM format?? I got this from a JE Mc Gimpsey Use =MAX(0, B2-0.75) Have two more questions : 1. Why the -0,75?? it works but could not understand why it works 2. IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and 21:00, and list only the hours between 18:00 and 21:00, and a new one from 21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00 between 18:00 and 21:00 the get 40% additional sallary and after 21:00 they get 100% more, would have bee SO nice to be able to make this calculation automatic -- Just a regular user |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many are at work every hour in a 24-hour day? | Excel Discussion (Misc queries) | |||
Converting from 12-hour to 24-hour format | Excel Worksheet Functions | |||
Overtime for 8 hour and 40 hour | Excel Discussion (Misc queries) | |||
convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
How can I round an hour to the nearest 1/4 hour? | Excel Worksheet Functions |