Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Hi I an trying to generate a formula to enable my wife to calculate the off
duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
A1 = 10:00
B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Hi Have tried copying thsi formula in to cell c, but it just comes up with
zero, what formate should the cells be, i have tried "time format" and general? any ideas "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Hi Again
Just had a look over the formula you have suggested to try, if there an issues with the "07:00" value as this is a time value and not a total between the times eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs threrefore 07:00 is a time value and would generate a zero?? "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Entering 10:00 to A1 will automatically custom format cell A1 to h:mm
Entering 22:00 to B1 will automatically custom format cell B1 to h:mm Custom format C1 to [h]:mm and try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi Again Just had a look over the formula you have suggested to try, if there an issues with the "07:00" value as this is a time value and not a total between the times eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs threrefore 07:00 is a time value and would generate a zero?? "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Fantastic that works great, however i had to set the cell format to "Time"
many thanks for for help "Jacob Skaria" wrote: Entering 10:00 to A1 will automatically custom format cell A1 to h:mm Entering 22:00 to B1 will automatically custom format cell B1 to h:mm Custom format C1 to [h]:mm and try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi Again Just had a look over the formula you have suggested to try, if there an issues with the "07:00" value as this is a time value and not a total between the times eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs threrefore 07:00 is a time value and would generate a zero?? "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Hi again its me sorry
That works great, but when i am trying to add all the totals together at the end coulb say "J" it does not work as it comes up with just adding the time, in stead of 10:00hrs to 22:00hrs equals 11:30 for Monday (less the .5 hr), for Tuesday it is 08:00hrs to 12:00hrs equals 4.0hrs (do not subtract the 0.5 hr as it is less than 7 hrs total), Wednesday 08:00hrs to 12:00hrs equals 4.0hrs there for the total for the week should read in say columb "J" total 19.5 hrs for that week formula used =SUM(C1+F1+I1) but it does not come up with this total? sorry to be a pain "Jacob Skaria" wrote: Entering 10:00 to A1 will automatically custom format cell A1 to h:mm Entering 22:00 to B1 will automatically custom format cell B1 to h:mm Custom format C1 to [h]:mm and try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi Again Just had a look over the formula you have suggested to try, if there an issues with the "07:00" value as this is a time value and not a total between the times eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs threrefore 07:00 is a time value and would generate a zero?? "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Firstly, could you please explain to us what you want the SUM function to do
in =SUM(C1+F1+I1) ? Why not just =C1+F1+I1 ? What do you want to sum with C1+F1+I1 ? Secondly, you say "does not come up with this total" but you haven't told us what total you have come up with, so it's rather difficult for us to tell what you've done wrong. Thirdly, in another reply to Jacob's message you said "i had to set the cell format to "Time"" What was wrong with Jacob's suggestion of: Custom format C1 to [h]:mm ? You will see that his suggestion copes with values beyond 24 hours when you add them up, but yours doesn't. -- David Biddulph "gadgetman" wrote in message ... Hi again its me sorry That works great, but when i am trying to add all the totals together at the end coulb say "J" it does not work as it comes up with just adding the time, in stead of 10:00hrs to 22:00hrs equals 11:30 for Monday (less the .5 hr), for Tuesday it is 08:00hrs to 12:00hrs equals 4.0hrs (do not subtract the 0.5 hr as it is less than 7 hrs total), Wednesday 08:00hrs to 12:00hrs equals 4.0hrs there for the total for the week should read in say columb "J" total 19.5 hrs for that week formula used =SUM(C1+F1+I1) but it does not come up with this total? sorry to be a pain "Jacob Skaria" wrote: Entering 10:00 to A1 will automatically custom format cell A1 to h:mm Entering 22:00 to B1 will automatically custom format cell B1 to h:mm Custom format C1 to [h]:mm and try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi Again Just had a look over the formula you have suggested to try, if there an issues with the "07:00" value as this is a time value and not a total between the times eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs threrefore 07:00 is a time value and would generate a zero?? "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Formula
Hi Again
I have seen the issue regarding to my last thread to you on the totaling issue. What seems to be happening is that when it gets to over the 24Hrs total it the carries on to count like a clock would. eg a total that should read 24hrs:30min in showing the total being 00:30 can you help to sort this totaling issue out? "Jacob Skaria" wrote: Entering 10:00 to A1 will automatically custom format cell A1 to h:mm Entering 22:00 to B1 will automatically custom format cell B1 to h:mm Custom format C1 to [h]:mm and try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi Again Just had a look over the formula you have suggested to try, if there an issues with the "07:00" value as this is a time value and not a total between the times eg 08:00hrs to 13:00 hrs equals a Total of 5 hrs threrefore 07:00 is a time value and would generate a zero?? "Jacob Skaria" wrote: A1 = 10:00 B1 = 22:00 In C1 try the below formula =IF(B1-A1TIMEVALUE("07:00"),B1-A1-TIMEVALUE("00:30"),B1-A1) If this post helps click Yes --------------- Jacob Skaria "gadgetman" wrote: Hi I an trying to generate a formula to enable my wife to calculate the off duty for her section. i have used 3 cells to start off with just to see if i could get it to work, cell 1 = start time, Cell 2 = end time, Cell 3 = total. e.g. start time =08.00, end time =13:00, then total time = 5hrs, formula say =sum(d3-d2) simple. Ok Well this is the part were i have got stuck. If the total is equal or more than 7 hours then i need to subtract 0.5hr, but only if it is equal or greater so say start time is 10:00, end time is 22:00 then is should = 11.5 and not 12 hrs as the sum is greater than 7hrs Can some kind person out there help with the formula cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|