I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Use the calculations that you indicated (A2-A1) but format the cell using the
following Time format:

[h]:mm:ss

Select the result cell, click FORMAT in the menu and select CELLS. In the
FORMAT CELLS dialog box locate the CUSTOM category in the category pane and
then locate the above format in the TYPE: list box. You can delete the ':ss'
if you do not want to see the seconds displayed.
--
Kevin Backmann


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

I'm still not getting it. It still has the same value and is giving #VALUE!
in the calculation.

Jason

"Kevin B" wrote:

Use the calculations that you indicated (A2-A1) but format the cell using the
following Time format:

[h]:mm:ss

Select the result cell, click FORMAT in the menu and select CELLS. In the
FORMAT CELLS dialog box locate the CUSTOM category in the category pane and
then locate the above format in the TYPE: list box. You can delete the ':ss'
if you do not want to see the seconds displayed.
--
Kevin Backmann


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a
pm entry so your pm suffix is what's causing the #VALUE error.
--
Kevin Backmann


"jbly" wrote:

I'm still not getting it. It still has the same value and is giving #VALUE!
in the calculation.

Jason

"Kevin B" wrote:

Use the calculations that you indicated (A2-A1) but format the cell using the
following Time format:

[h]:mm:ss

Select the result cell, click FORMAT in the menu and select CELLS. In the
FORMAT CELLS dialog box locate the CUSTOM category in the category pane and
then locate the above format in the TYPE: list box. You can delete the ':ss'
if you do not want to see the seconds displayed.
--
Kevin Backmann


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Is there a way to calculate without having to go through and take out the AM
and PM? It's no difference that what I'm doing now. I enter the data in as 24
hour format manually. If I delete the PM, then it doesn't know to add 12
hours to the data. I want to have it convert that 4:43 PM to a 24 hour format
automatically.

Jason

"Kevin B" wrote:

Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a
pm entry so your pm suffix is what's causing the #VALUE error.
--
Kevin Backmann


"jbly" wrote:

I'm still not getting it. It still has the same value and is giving #VALUE!
in the calculation.

Jason

"Kevin B" wrote:

Use the calculations that you indicated (A2-A1) but format the cell using the
following Time format:

[h]:mm:ss

Select the result cell, click FORMAT in the menu and select CELLS. In the
FORMAT CELLS dialog box locate the CUSTOM category in the category pane and
then locate the above format in the TYPE: list box. You can delete the ':ss'
if you do not want to see the seconds displayed.
--
Kevin Backmann


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Format the cell you enter the time into as a 24 hour clock format. You can
enter 4:33 pm and the format will modify the display to 16:33:00, but you
cannot enter 16:33 pm as it's not a valid time entry.
--
Kevin Backmann


"jbly" wrote:

Is there a way to calculate without having to go through and take out the AM
and PM? It's no difference that what I'm doing now. I enter the data in as 24
hour format manually. If I delete the PM, then it doesn't know to add 12
hours to the data. I want to have it convert that 4:43 PM to a 24 hour format
automatically.

Jason

"Kevin B" wrote:

Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a
pm entry so your pm suffix is what's causing the #VALUE error.
--
Kevin Backmann


"jbly" wrote:

I'm still not getting it. It still has the same value and is giving #VALUE!
in the calculation.

Jason

"Kevin B" wrote:

Use the calculations that you indicated (A2-A1) but format the cell using the
following Time format:

[h]:mm:ss

Select the result cell, click FORMAT in the menu and select CELLS. In the
FORMAT CELLS dialog box locate the CUSTOM category in the category pane and
then locate the above format in the TYPE: list box. You can delete the ':ss'
if you do not want to see the seconds displayed.
--
Kevin Backmann


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Try

=IF(A2A1,(A2-A1),(A2+24)-A1)
--
Regards
Warren


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for
the day. I want to paste this into Excel and have it calculate how much time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552 hours
beyond what you wanted? (Though you may be fooled as to what answer you've
got if you format as h:mm rather than as [h]:mm, and therefore don't display
the number of whole days.)

Another way is =MOD(A2-A1,1)
--
David Biddulph

"Warren Easton" wrote in message
...
Try

=IF(A2A1,(A2-A1),(A2+24)-A1)
--
Regards
Warren


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference?
We
have a network in/out board. I get a report of when I clocked in and out
for
the day. I want to paste this into Excel and have it calculate how much
time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

Hi David,

This formula was for day shifts and night shifts the +24 was for going
through 12 midnight, the +1 may also work but I used the +24, it was a while
ago.
--
Regards
Warren


"David Biddulph" wrote:

Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552 hours
beyond what you wanted? (Though you may be fooled as to what answer you've
got if you format as h:mm rather than as [h]:mm, and therefore don't display
the number of whole days.)

Another way is =MOD(A2-A1,1)
--
David Biddulph

"Warren Easton" wrote in message
...
Try

=IF(A2A1,(A2-A1),(A2+24)-A1)
--
Regards
Warren


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time difference?
We
have a network in/out board. I get a report of when I clocked in and out
for
the day. I want to paste this into Excel and have it calculate how much
time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

You need to remember that Excel stores times in days, not hours, so +1 adds
1 day to cope with going through midnight, +24 would add 24 days, and
therefore would give you an answer in error by 552 hours.
--
David Biddulph

"Warren Easton" wrote in message
...
Hi David,

This formula was for day shifts and night shifts the +24 was for going
through 12 midnight, the +1 may also work but I used the +24, it was a
while
ago.
--
Regards
Warren


"David Biddulph" wrote:

Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552
hours
beyond what you wanted? (Though you may be fooled as to what answer
you've
got if you format as h:mm rather than as [h]:mm, and therefore don't
display
the number of whole days.)

Another way is =MOD(A2-A1,1)
--
David Biddulph

"Warren Easton" wrote in message
...
Try

=IF(A2A1,(A2-A1),(A2+24)-A1)
--
Regards
Warren


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time
difference?
We
have a network in/out board. I get a report of when I clocked in and
out
for
the day. I want to paste this into Excel and have it calculate how
much
time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help

You're right, Thanks.
--
Regards
Warren


"David Biddulph" wrote:

You need to remember that Excel stores times in days, not hours, so +1 adds
1 day to cope with going through midnight, +24 would add 24 days, and
therefore would give you an answer in error by 552 hours.
--
David Biddulph

"Warren Easton" wrote in message
...
Hi David,

This formula was for day shifts and night shifts the +24 was for going
through 12 midnight, the +1 may also work but I used the +24, it was a
while
ago.
--
Regards
Warren


"David Biddulph" wrote:

Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552
hours
beyond what you wanted? (Though you may be fooled as to what answer
you've
got if you format as h:mm rather than as [h]:mm, and therefore don't
display
the number of whole days.)

Another way is =MOD(A2-A1,1)
--
David Biddulph

"Warren Easton" wrote in message
...
Try

=IF(A2A1,(A2-A1),(A2+24)-A1)
--
Regards
Warren


"jbly" wrote:

I can't seem to find it in the help. How do I calculate time
difference?
We
have a network in/out board. I get a report of when I clocked in and
out
for
the day. I want to paste this into Excel and have it calculate how
much
time
I worked. It comes in as:

A1 07:38 AM
A2 10:55 AM
A3 12:01 AM
A4 16:43 PM

I want it to calculate A2-A1 and A4-A3. Please help