| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Another Time Calulation Question - Sorry.
Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling
short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
Instead of the way you are doing it, remember that Excel stores dates and
times as numbers. Dates as an integer (the number of dates since Jan 1st 1900?), and times as a fraction (8AM = .3333...) Simply subtract the start time from the end time. Also subtract any time off for lunch and breaks that should not be counted. The result might look funny as Excel might format the resulting cell as a date. Format THAT cell as a number and you'll have your elapsed time. "Dugster" wrote: Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
Actually, do this extra step - multiply by 24 (hours per day). The resulting
formula should look like =(E14-D14)*24 where E14 is the end time, D14 is the start time "Dugster" wrote: Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
If you really need to use that structure for recording time...
try this: I4: =INT((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24) J4: =MOD((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24,1)*60 copy those formulas down as far as you need. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dugster" wrote in message ... Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
Thanks Ron, The minutes are calculating a treat (J4) :) The hours though
(I4) is returning all zero's. But thanks for what I got so far. -- I don't mind being wrong, I'd just like you to prove it! :) "Ron Coderre" wrote: If you really need to use that structure for recording time... try this: I4: =INT((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24) J4: =MOD((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24,1)*60 copy those formulas down as far as you need. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dugster" wrote in message ... Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
Thanks Flick. I just didn't get it though. I did say I was a filling short
of a sandwich though. :) -- I don't mind being wrong, I'd just like you to prove it! :) "Flick Olmsford" wrote: Instead of the way you are doing it, remember that Excel stores dates and times as numbers. Dates as an integer (the number of dates since Jan 1st 1900?), and times as a fraction (8AM = .3333...) Simply subtract the start time from the end time. Also subtract any time off for lunch and breaks that should not be counted. The result might look funny as Excel might format the resulting cell as a date. Format THAT cell as a number and you'll have your elapsed time. "Dugster" wrote: Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
Hi again Ron, ignore my last message; I just changed the cell format and it's
perfect. Thank you very much for your help. You guys are great. Doug -- I don't mind being wrong, I'd just like you to prove it! :) "Ron Coderre" wrote: If you really need to use that structure for recording time... try this: I4: =INT((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24) J4: =MOD((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24,1)*60 copy those formulas down as far as you need. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dugster" wrote in message ... Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Another Time Calulation Question - Sorry.
Thanks for the update, Doug...I'm glad we could help.
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dugster" wrote in message ... Hi again Ron, ignore my last message; I just changed the cell format and it's perfect. Thank you very much for your help. You guys are great. Doug -- I don't mind being wrong, I'd just like you to prove it! :) "Ron Coderre" wrote: If you really need to use that structure for recording time... try this: I4: =INT((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24) J4: =MOD((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24,1)*60 copy those formulas down as far as you need. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dugster" wrote in message ... Hi, I hope you don't mind a newbie asking newbie questions? :) I'm a filling short of a sandwich when it comes to excel, so please keep that in mind if you decide to be so kind as to respond. Thanks! I am trying to get the total hours and minutes worked over two periods to show in columns I and J. Honest, I've used the help file and looked about here but I just don't get it. :s I'd appreciate all and any help. Thanks. A B C D E F G H I J 1 AM AM PM PM Deficit B/F 2 Start Finish Start Finish 3 Hrs Mins Hrs | Mins 4 09 | 10 12 | 30 13 | 00 17 | 00 5 08 | 30 12 | 30 13 | 00 17 | 30 6 09 | 00 12 | 00 12 | 40 17 | 00 -- I don't mind being wrong, I'd just like you to prove it! :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I hide a calulation error in cells/table #div/0! | Excel Discussion (Misc queries) | |||
| Getpivot calulation | Excel Worksheet Functions | |||
| Time Question | Excel Discussion (Misc queries) | |||
| Time question | Excel Discussion (Misc queries) | |||
| Time Question | Excel Worksheet Functions | |||
Linear Mode
