I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

10:00 PM Friday 3:00 AM Sun

Start in A1 end in B1

if the dates are included just do


=B1-A1

then format result as [hh]:mm:ss

if less than 24 hours but start time later than end times, e.g. start time
08:00 PM and end time 06:00 AM


=B1-A1+(A1B1)

or

=MOD(B1-A1,1)

format as time


--


Regards,


Peo Sjoblom




"wahoos" wrote in message
...
I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

10:00 PM Friday 3:00 AM Sun

You can't just say

10:00 PM Friday 3:00 AM Sun
you need a date and a time and then it's simply

=b1-a1

where B1 is the later date/time

format the answer as [hh]:mm

Mike

"wahoos" wrote:

I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

If you have start and end times in Excel date and time (formatted in your
example as h:mm AM/PM dddd), then you can just use =B2-A2, and format the
result as [h]:mm to show a time beyond 24 hours.
--
David Biddulph

"wahoos" wrote in message
...
I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

10:00 PM Friday 3:00 AM Sun

thanks. Is there a way to still find the elapsed time when the date or day is
not part of the formula in each cell. The scenario is a daily schedule, so
the assumption is all jobs start on that particular day, say Friday in the
original question. I don't want to have to enter Friday in each cell, but
still want to be able to capture jobs that ran for over 24 hours accurately.
Cell A1 is start time, B1 is end time, and C1 is elapsed time.

"Peo Sjoblom" wrote:

Start in A1 end in B1

if the dates are included just do


=B1-A1

then format result as [hh]:mm:ss

if less than 24 hours but start time later than end times, e.g. start time
08:00 PM and end time 06:00 AM


=B1-A1+(A1B1)

or

=MOD(B1-A1,1)

format as time


--


Regards,


Peo Sjoblom




"wahoos" wrote in message
...
I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

10:00 PM Friday 3:00 AM Sun

How would I format the fields so excel would know when I entered 3:00AM- it
was Sunday, when the job was started on Friday at 10PM?

"Mike H" wrote:

You can't just say

10:00 PM Friday 3:00 AM Sun
you need a date and a time and then it's simply

=b1-a1

where B1 is the later date/time

format the answer as [hh]:mm

Mike

"wahoos" wrote:

I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

All of the replies assume you have the full date and time in the cells, and
your follow ups suggest that you do not. If there is no date, then Excel has
no way to know what you want to do. You can of course add stuff to your
formulas that add 1 to A-B if B is less than A, but if your times span more
than one midnight, or even if they span more than 24 hours, this isn't good
enough.

You should either go back and fix your times so they also include a date
(and always do this henceforth), or use another pair of columns to contain
the dates, and use these in your subtraction.

Maybe this sounds like a pain. but Excel works best when the data it is fed
is "good" data. You can spend ten minutes with your data, or ten hours
trying to fix the results.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"wahoos" wrote in message
...
I'm trying to find the difference between start time and end time when the
total time will exceed a 24 hour period.

10:00 PM Friday 3:00 AM Sun

Sorry, but the extra-sensory perception facility in Excel hasn't been
introduced yet. It can't guess what you wanted. You need to tell it
(either individually when you enter data in a cell, or by defining an
algorithm by which the day can be determined based on other data). How is
Excel supposed to guess that when you said 3:00 AM you meant Sunday, not
Saturday or Wednesday?
--
David Biddulph

"wahoos" wrote in message
...
How would I format the fields so excel would know when I entered 3:00AM-
it
was Sunday, when the job was started on Friday at 10PM?

"Mike H" wrote:

You can't just say

10:00 PM Friday 3:00 AM Sun
you need a date and a time and then it's simply

=b1-a1

where B1 is the later date/time

format the answer as [hh]:mm

Mike

"wahoos" wrote:

I'm trying to find the difference between start time and end time when
the
total time will exceed a 24 hour period.

Sorry, but the extra-sensory perception facility in Excel hasn't been
introduced yet.

ROFL

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Sorry, but the extra-sensory perception facility in Excel hasn't been
introduced yet. It can't guess what you wanted. You need to tell it
(either individually when you enter data in a cell, or by defining an
algorithm by which the day can be determined based on other data). How is
Excel supposed to guess that when you said 3:00 AM you meant Sunday, not
Saturday or Wednesday?
--
David Biddulph

"wahoos" wrote in message
...
How would I format the fields so excel would know when I entered 3:00AM-
it
was Sunday, when the job was started on Friday at 10PM?

"Mike H" wrote:

You can't just say

10:00 PM Friday 3:00 AM Sun
you need a date and a time and then it's simply

=b1-a1

where B1 is the later date/time

format the answer as [hh]:mm

Mike

"wahoos" wrote:

I'm trying to find the difference between start time and end time when
the
total time will exceed a 24 hour period.