Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate cells with format hh:mm
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 IN OUT HOURS AFTER 18 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?? -- Just a regular user |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate cells with format hh:mm
One way:
D2: =MAX(0,B2-TIME(18,0,0)) or, equivalently: D2: =MAX(0, B2-0.75) Format D2 as time. In article , 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 IN OUT HOURS AFTER 18 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?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate cells with format hh:mm
Thank you it worked, the =MAX(0, B2-0.75) worked but could not make the other
one work. 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 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 Thanks for the efford, and I clicked YES on your first reply, and help :) -- Just a regular user JE McGimpsey skrev: One way: D2: =MAX(0,B2-TIME(18,0,0)) or, equivalently: D2: =MAX(0, B2-0.75) Format D2 as time. In article , 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 IN OUT HOURS AFTER 18 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?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate cells with format hh:mm
It works because XL stores times as fractional days. 18:00 is 18/24ths
of a day, or 0.75. No idea what "I clicked YES" means, but thanks, I guess... In article , Rockbear wrote: Thank you it worked, the =MAX(0, B2-0.75) worked but could not make the other one work. 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 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 Thanks for the efford, and I clicked YES on your first reply, and help :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate cells with format hh:mm
Clicked yes means that your relply was helpful to me :), thank you
-- Just a regular user JE McGimpsey skrev: It works because XL stores times as fractional days. 18:00 is 18/24ths of a day, or 0.75. No idea what "I clicked YES" means, but thanks, I guess... In article , Rockbear wrote: Thank you it worked, the =MAX(0, B2-0.75) worked but could not make the other one work. 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 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 Thanks for the efford, and I clicked YES on your first reply, and help :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How calculate difference between [t]:mm-format and general format | Excel Worksheet Functions | |||
How can I calculate hours into pay...in this format hh:mm:ss | Excel Discussion (Misc queries) | |||
Format Cells to calculate hours and minutes taken to complete task | Excel Worksheet Functions | |||
want format cells alignment not format cells font style | Excel Discussion (Misc queries) | |||
How do I only calculate cells which have a filled color format? | Excel Discussion (Misc queries) |