Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
Hopefully someone can stay with me on this. I have colums A-H and 8 rows.
In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
try this in column G
=F2-C2-(E2-D2) Ensure it's formatted as hh:mm Mike "Cobra" wrote: Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
You don't say exactly how you want your answer presented or if you will use
the result in another calculation. Mike gave you a way to display the value as hours:minutes, but the number in the cell is still the fraction of a day. So, if you want to use it in a calculation, you will have to remember to multiply it by 24 within that calculation. However, if you want to display the decimal number of hours directly (and in a form usable later on directly in calculations), you could use this formula... =24*(F2-C2-(E2-D2)) or, without the extra parentheses... =24*(F2-C2-E2+D2) Rick "Cobra" wrote in message ... Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
Wrote this formula for work so they don't have to worry about the am/pm thing
and because we need partial hours to be decimal - you may find it helpful: =IF(D2-C2<0,D2-C2+0.5,D2-C2)*24+IF(F2-E2<0,F2-E2+0.5,F2-E2)*24 time should be entered in the hh:mm format. Format G as number. "Cobra" wrote: Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
Ok I appreciate all the help but unfortunatly none of them worked. I think
the hold up here will be in colums D & E if there is NO lunch taken, I am entering "NO LUNCH" The formula appears to work fine when it's =24*(F2-C2-E2+D2). It's that NO lunch thing "BoniM" wrote: Wrote this formula for work so they don't have to worry about the am/pm thing and because we need partial hours to be decimal - you may find it helpful: =IF(D2-C2<0,D2-C2+0.5,D2-C2)*24+IF(F2-E2<0,F2-E2+0.5,F2-E2)*24 time should be entered in the hh:mm format. Format G as number. "Cobra" wrote: Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
=IF(D2="NO LUNCH",(F2-C2)*24,(F2-C2-E2+D2)*24)
That should fix it... "Cobra" wrote: Ok I appreciate all the help but unfortunatly none of them worked. I think the hold up here will be in colums D & E if there is NO lunch taken, I am entering "NO LUNCH" The formula appears to work fine when it's =24*(F2-C2-E2+D2). It's that NO lunch thing "BoniM" wrote: Wrote this formula for work so they don't have to worry about the am/pm thing and because we need partial hours to be decimal - you may find it helpful: =IF(D2-C2<0,D2-C2+0.5,D2-C2)*24+IF(F2-E2<0,F2-E2+0.5,F2-E2)*24 time should be entered in the hh:mm format. Format G as number. "Cobra" wrote: Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
Bonim, again it works fine in the column(s) where there is a lunch time,
however it does not work where the "NO LUNCH" is entered. It says "A value used in the formula is of the wrong data type" Would it be possible to send you the work sheet that I am working on ? "BoniM" wrote: =IF(D2="NO LUNCH",(F2-C2)*24,(F2-C2-E2+D2)*24) That should fix it... "Cobra" wrote: Ok I appreciate all the help but unfortunatly none of them worked. I think the hold up here will be in colums D & E if there is NO lunch taken, I am entering "NO LUNCH" The formula appears to work fine when it's =24*(F2-C2-E2+D2). It's that NO lunch thing "BoniM" wrote: Wrote this formula for work so they don't have to worry about the am/pm thing and because we need partial hours to be decimal - you may find it helpful: =IF(D2-C2<0,D2-C2+0.5,D2-C2)*24+IF(F2-E2<0,F2-E2+0.5,F2-E2)*24 time should be entered in the hh:mm format. Format G as number. "Cobra" wrote: Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction/addition
Sure - send it to boni at msn dot com...
"Cobra" wrote: Bonim, again it works fine in the column(s) where there is a lunch time, however it does not work where the "NO LUNCH" is entered. It says "A value used in the formula is of the wrong data type" Would it be possible to send you the work sheet that I am working on ? "BoniM" wrote: =IF(D2="NO LUNCH",(F2-C2)*24,(F2-C2-E2+D2)*24) That should fix it... "Cobra" wrote: Ok I appreciate all the help but unfortunatly none of them worked. I think the hold up here will be in colums D & E if there is NO lunch taken, I am entering "NO LUNCH" The formula appears to work fine when it's =24*(F2-C2-E2+D2). It's that NO lunch thing "BoniM" wrote: Wrote this formula for work so they don't have to worry about the am/pm thing and because we need partial hours to be decimal - you may find it helpful: =IF(D2-C2<0,D2-C2+0.5,D2-C2)*24+IF(F2-E2<0,F2-E2+0.5,F2-E2)*24 time should be entered in the hh:mm format. Format G as number. "Cobra" wrote: Hopefully someone can stay with me on this. I have colums A-H and 8 rows. In colum C is the start time of my shift ex) 9am; in column D I have my lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and in column F I have my Finish time ex) 5:30 What I want is in column G is the total number of hrs for that day and I do not know the formula for this. So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of XXXX which should be column G Hope someone can help thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtraction | Excel Worksheet Functions | |||
simple subtraction | New Users to Excel | |||
subtraction formula | Excel Worksheet Functions | |||
Error in Addition and Subtraction | Excel Discussion (Misc queries) | |||
Subtraction Question | Excel Discussion (Misc queries) |