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! :)

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! :)

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! :)

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! :)

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! :)

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! :)

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! :)

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! :)