Is there a way to calculate the time quickly in Excel 2007? For example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file has a
Start and Stop time mixed together depending on when the employee clocks in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

This will give you the time for John Doe

=SUMPRODUCT(--(C3:C14="Stop"),--(D3:D14="John
Doe"),B3:B14)-SUMPRODUCT(--(C3:C14="Start"),--(D3:D14="John Doe"),B3:B14)


Where B3:B14 are the times, C3:C14 are the status and D3:D14 are the
employees

format as hh:mm:ss


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For
example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file has a
Start and Stop time mixed together depending on when the employee clocks
in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

I typed the command and nothing happened. The new cell displays the formula
and did not give me the result. Do you have any suggestions?

Thanks.

"Peo Sjoblom" wrote in message
...
This will give you the time for John Doe

=SUMPRODUCT(--(C3:C14="Stop"),--(D3:D14="John
Doe"),B3:B14)-SUMPRODUCT(--(C3:C14="Start"),--(D3:D14="John Doe"),B3:B14)


Where B3:B14 are the times, C3:C14 are the status and D3:D14 are the
employees

format as hh:mm:ss


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For
example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file has a
Start and Stop time mixed together depending on when the employee clocks
in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

Your cell is formatted as text. Format as general, and re-enter the
formula.
--
David Biddulph

"Jill Johnson" wrote in message
...
I typed the command and nothing happened. The new cell displays the
formula
and did not give me the result. Do you have any suggestions?

Thanks.

"Peo Sjoblom" wrote in message
...
This will give you the time for John Doe

=SUMPRODUCT(--(C3:C14="Stop"),--(D3:D14="John
Doe"),B3:B14)-SUMPRODUCT(--(C3:C14="Start"),--(D3:D14="John Doe"),B3:B14)


Where B3:B14 are the times, C3:C14 are the status and D3:D14 are the
employees

format as hh:mm:ss


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For
example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file has
a
Start and Stop time mixed together depending on when the employee
clocks
in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

Try this one.
http://www.savefile.com/files/1795669


"Jill Johnson" wrote:

I typed the command and nothing happened. The new cell displays the formula
and did not give me the result. Do you have any suggestions?

Thanks.

"Peo Sjoblom" wrote in message
...
This will give you the time for John Doe

=SUMPRODUCT(--(C3:C14="Stop"),--(D3:D14="John
Doe"),B3:B14)-SUMPRODUCT(--(C3:C14="Start"),--(D3:D14="John Doe"),B3:B14)


Where B3:B14 are the times, C3:C14 are the status and D3:D14 are the
employees

format as hh:mm:ss


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For
example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file has a
Start and Stop time mixed together depending on when the employee clocks
in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

Hi Peo
This formula works fine for the time within the same day

can you pl. tell me how to calculate time difference for night shifts
The employee starts at 19:00 hrs today and end time is 7:00 AM tomorrow

what formula do I use for this?
Please help me find it...

Barnabas

"Jill Johnson" wrote:

Is there a way to calculate the time quickly in Excel 2007? For example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

You appear to have replied to Jill's original post, not to Peo's.

If you intended to refer to the post where Peo said =B1-A1, and if your
times don't include the date information, then try =MOD(B1-A1,1)
--
David Biddulph

"Barnabas" wrote in message
...

Hi Peo
This formula works fine for the time within the same day

can you pl. tell me how to calculate time difference for night shifts
The employee starts at 19:00 hrs today and end time is 7:00 AM tomorrow

what formula do I use for this?
Please help me find it...

Barnabas

"Jill Johnson" wrote:

Is there a way to calculate the time quickly in Excel 2007? For example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

Hi David
Wow! Beautiful shot!
I got it.. and thanx a million
Barnabas

"David Biddulph" wrote:

You appear to have replied to Jill's original post, not to Peo's.

If you intended to refer to the post where Peo said =B1-A1, and if your
times don't include the date information, then try =MOD(B1-A1,1)
--
David Biddulph

"Barnabas" wrote in message
...

Hi Peo
This formula works fine for the time within the same day

can you pl. tell me how to calculate time difference for night shifts
The employee starts at 19:00 hrs today and end time is 7:00 AM tomorrow

what formula do I use for this?
Please help me find it...

Barnabas

"Jill Johnson" wrote:

Is there a way to calculate the time quickly in Excel 2007? For example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want to know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

David,

Thanks for your response. It was my error. I had the formula on two
separate lines.

Peo,

May I ask you more questions? Is C3 the first line of cell for Start/Stop
Time and C14 is the last cell of Start/Stop Time? The formula works when
the Start and Stop time for the same employee are consecutive. For example,
if the sequence is like below

Date Start/Stop Time Status Employee
9/17/08 6:35 am Start John Doe
9/17/08 4:35 pm Stop John Doe

If there is another employee between the Start and Stop time for John Doe,
the formular does not work correctly. Please let me know if I am wrong.
For example between John Doe, there is a record for Allan Sheep

Date Start/Stop Time Status Employee
9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 4:35 pm Stop John Doe

Thanks.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Your cell is formatted as text. Format as general, and re-enter the
formula.
--
David Biddulph

"Jill Johnson" wrote in message
...
I typed the command and nothing happened. The new cell displays the
formula
and did not give me the result. Do you have any suggestions?

Thanks.

"Peo Sjoblom" wrote in message
...
This will give you the time for John Doe

=SUMPRODUCT(--(C3:C14="Stop"),--(D3:D14="John
Doe"),B3:B14)-SUMPRODUCT(--(C3:C14="Start"),--(D3:D14="John
Doe"),B3:B14)


Where B3:B14 are the times, C3:C14 are the status and D3:D14 are the
employees

format as hh:mm:ss


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For
example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file
has
a
Start and Stop time mixed together depending on when the employee
clocks
in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want
to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.

Thanks.

"Jman" wrote in message
...

Try this one.
http://www.savefile.com/files/1795669


"Jill Johnson" wrote:

I typed the command and nothing happened. The new cell displays the
formula
and did not give me the result. Do you have any suggestions?

Thanks.

"Peo Sjoblom" wrote in message
...
This will give you the time for John Doe

=SUMPRODUCT(--(C3:C14="Stop"),--(D3:D14="John
Doe"),B3:B14)-SUMPRODUCT(--(C3:C14="Start"),--(D3:D14="John
Doe"),B3:B14)


Where B3:B14 are the times, C3:C14 are the status and D3:D14 are the
employees

format as hh:mm:ss


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Thanks for your prompt response.

If the start time and end time are in rows, what would you do? For
example,
John Doe clocks in at 7:58 am and clocks out at 4:35 pm. The file
has a
Start and Stop time mixed together depending on when the employee
clocks
in
and out. Thanks.

Date Start/Stop Time Status Employee

9/17/08 6:35 am Start John Doe
9/17/08 6:41 am Start Allan Sheep
9/17/08 6:42 am Stop Steve Palmer
9/17/08 6:42 am Start Robert Conelly
9/17/08 4:35 pm Stop John Doe
9/17/08 4:36 pm Start Joseph Smith
9/17/08 4:37 pm Stop Robert Conelly
9/17/08 4:39 pm Stop Allan Sheep
9/17/08 4:45 pm Start Art Miller
9/17/08 4:48 pm Stop Joseph Smith

"Peo Sjoblom" wrote in message
...
=B1-A1

format as hh:mm:ss

where B1 is end and A1 start

if there can be more than 24 hours use

[h]:mm:ss

as a format


--


Regards,


Peo Sjoblom



"Jill Johnson" wrote in message
...
Is there a way to calculate the time quickly in Excel 2007? For
example,
the employee clocks in 7:58 am and clocks out at 4:35 pm. I want
to
know
how many hours, minutes, and seconds between 7:58 am and 4:35 pm.

Thanks.