| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula Needed
I need a formula that will calculate some time for me. Here is what I have.
I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formula Needed
=B1-A1-C1/24
or. if lunchtime is eneterd as # of minutes: =B1-A1-C1/24/60 <will the formula return 7.5 if the user had a half hour? No, 8:30 :-) -- Kind regards, Niek Otten Microsoft MVP - Excel "Kris" wrote in message ... I need a formula that will calculate some time for me. Here is what I have. I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formula Needed
Is it possible instead of it showing time that it show a number. For
example, 5 PM - 8 AM - 1 Hour lunch = 8.0 hours worked or 5 PM - 7:30 AM - 1 Hour lunch = 8.5 hours worked "Kris" wrote: I need a formula that will calculate some time for me. Here is what I have. I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formula Needed
=(B1-A1-C1/24/60)*24&" hours worked"
-- Kind regards, Niek Otten Microsoft MVP - Excel "Kris" wrote in message ... Is it possible instead of it showing time that it show a number. For example, 5 PM - 8 AM - 1 Hour lunch = 8.0 hours worked or 5 PM - 7:30 AM - 1 Hour lunch = 8.5 hours worked "Kris" wrote: I need a formula that will calculate some time for me. Here is what I have. I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formula Needed
And format as General, not time
-- Kind regards, Niek Otten Microsoft MVP - Excel "Kris" wrote in message ... Is it possible instead of it showing time that it show a number. For example, 5 PM - 8 AM - 1 Hour lunch = 8.0 hours worked or 5 PM - 7:30 AM - 1 Hour lunch = 8.5 hours worked "Kris" wrote: I need a formula that will calculate some time for me. Here is what I have. I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formula Needed
Multiply the result (see the other answers you have had) by 24, and format
as general or number, not as time. -- David Biddulph "Kris" wrote in message ... Is it possible instead of it showing time that it show a number. For example, 5 PM - 8 AM - 1 Hour lunch = 8.0 hours worked or 5 PM - 7:30 AM - 1 Hour lunch = 8.5 hours worked "Kris" wrote: I need a formula that will calculate some time for me. Here is what I have. I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time formula Needed
Please remember, do not double post.
-- ** John C ** "Kris" wrote: I need a formula that will calculate some time for me. Here is what I have. I have a box where a user types in 8:00 AM meaning they start worked at 8:00 in the morning. They then type in 5:00 PM for when they end work. There also is a cell that they input their amount of time at lunch, say 1 hour. I need the formula to subtract 8 Am from 5 PM and then subtract the 1 hour lunch. So I should get 8 hours. Also, will the formula return 7.5 if the user had a half hour? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| time tracking formula needed | Excel Worksheet Functions | |||
| Time format help needed | Excel Worksheet Functions | |||
| time guru needed | Excel Worksheet Functions | |||
| Formula needed - automatically calculate session time totals for each user | Excel Worksheet Functions | |||
| Formula Needed for Transit Time | Excel Discussion (Misc queries) | |||
Linear Mode
