Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
i have a problem when wanting to calculate time using 24 hour clock, i have
attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Try this instead of your formula:
=IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Hi
Standard durationformula for work past midnight -or not- is =B1-A1+(B1<A1) -I think they deserve a paid break working hours like that ;-) HTH. Best wishes harald "mats" skrev i melding ... i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Pete,
That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Which hours do the night rate apply to, and which hours would be
classed as day rate? Pete mats wrote: Pete, That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Pete,
11pm to 7am are classed as the night rate. tks Mats "Pete_UK" wrote: Which hours do the night rate apply to, and which hours would be classed as day rate? Pete mats wrote: Pete, That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Is anyone likely to work a combination of day and night hours at the
same time? For example, starting at 9pm and working through to 1am, or starting 6am till 12 noon? If not, then we could just use your start time column to determine a "shift" - 1 for day, 2 for night, then use this to determine which rate of pay should be applied to the hours. Hope this helps. Pete mats wrote: Pete, 11pm to 7am are classed as the night rate. tks Mats "Pete_UK" wrote: Which hours do the night rate apply to, and which hours would be classed as day rate? Pete mats wrote: Pete, That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Pete,
I doubt it very much that any person would be working a combination of day & night hours, so how would i do this? Tks "Pete_UK" wrote: Is anyone likely to work a combination of day and night hours at the same time? For example, starting at 9pm and working through to 1am, or starting 6am till 12 noon? If not, then we could just use your start time column to determine a "shift" - 1 for day, 2 for night, then use this to determine which rate of pay should be applied to the hours. Hope this helps. Pete mats wrote: Pete, 11pm to 7am are classed as the night rate. tks Mats "Pete_UK" wrote: Which hours do the night rate apply to, and which hours would be classed as day rate? Pete mats wrote: Pete, That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
You will need an extra column to determine which Rate to use (assume
column F), and a formula something like this: =IF(AND(A17/24,A1<23/24),"Day","Night") i.e. if the start time is after 7:00am but before 11:00pm then set to Day otherwise Night. It would be easier to return the actual rates/hour rather than the text, so that in the next column you only need to multiply the hours by the rate to get the pay. So, guessing the rates at 5.25 units/hour and 6.4 units per hour, you would have in coluumn F: =IF(AND(A1<7/24,A1<23/24),5.25,6.4) and in the next column: =E1*F1 where column E contains the hours from the previous formula. Obviously, both formulae should be copied down, and you might like to format them to show currency. Hope this helps. Pete mats wrote: Pete, I doubt it very much that any person would be working a combination of day & night hours, so how would i do this? Tks "Pete_UK" wrote: Is anyone likely to work a combination of day and night hours at the same time? For example, starting at 9pm and working through to 1am, or starting 6am till 12 noon? If not, then we could just use your start time column to determine a "shift" - 1 for day, 2 for night, then use this to determine which rate of pay should be applied to the hours. Hope this helps. Pete mats wrote: Pete, 11pm to 7am are classed as the night rate. tks Mats "Pete_UK" wrote: Which hours do the night rate apply to, and which hours would be classed as day rate? Pete mats wrote: Pete, That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating time
Slight amendment:
=IF(AND(A1=7/24,A1<23/24),5.25,6.4) This will include 7:00am start time as being at day rate. Pete Pete_UK wrote: You will need an extra column to determine which Rate to use (assume column F), and a formula something like this: =IF(AND(A17/24,A1<23/24),"Day","Night") i.e. if the start time is after 7:00am but before 11:00pm then set to Day otherwise Night. It would be easier to return the actual rates/hour rather than the text, so that in the next column you only need to multiply the hours by the rate to get the pay. So, guessing the rates at 5.25 units/hour and 6.4 units per hour, you would have in coluumn F: =IF(AND(A1<7/24,A1<23/24),5.25,6.4) and in the next column: =E1*F1 where column E contains the hours from the previous formula. Obviously, both formulae should be copied down, and you might like to format them to show currency. Hope this helps. Pete mats wrote: Pete, I doubt it very much that any person would be working a combination of day & night hours, so how would i do this? Tks "Pete_UK" wrote: Is anyone likely to work a combination of day and night hours at the same time? For example, starting at 9pm and working through to 1am, or starting 6am till 12 noon? If not, then we could just use your start time column to determine a "shift" - 1 for day, 2 for night, then use this to determine which rate of pay should be applied to the hours. Hope this helps. Pete mats wrote: Pete, 11pm to 7am are classed as the night rate. tks Mats "Pete_UK" wrote: Which hours do the night rate apply to, and which hours would be classed as day rate? Pete mats wrote: Pete, That worked perfectly, but i have another problem on it, The night hours needs to be calculated using a different rate, so i thought that i could have 2 totals, splitting the hours into the night rate & day rate, how can i do this? "Pete_UK" wrote: Try this instead of your formula: =IF(B1-A1<0,((B1-A1+1)*24)-D1,((B1-A1)*24)-D1) Hope this helps. Pete mats wrote: i have a problem when wanting to calculate time using 24 hour clock, i have attached a simple example: These are the columns & row 1: A B C D E Start Finish Time of break Break length Hours 8:00 18:00 13:00 1.00 9.00 23:00 7:00 2:00 1.00 ERROR I want to be able to use it for night time hours, the example that is there, I want to calculate from 11pm to 7am, with 1 hour break at 2am, how can I do this? The formula that i am using is as follows: Format of cells is 13:30 =IF(B1-A1<0,"ERROR",((B1-A1)*24)-D1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Response Time | Excel Discussion (Misc queries) | |||
calculating time | Excel Discussion (Misc queries) | |||
help with calculating overtime in a time sheet | Excel Discussion (Misc queries) | |||
Calculating a rate for elapsed time? | Excel Discussion (Misc queries) | |||
Calculating tvl time in Excel 2000 Rev.9.2720 | Excel Worksheet Functions |