date calculation between two times
 

I have a chart setup to calculate the converted time between to time frames
example. The crew started at 7:00am and ended at 10:00 am. Everything works
except with the time is started at 7:15 am and ended at 7:00 am which of
course is not possible but because it is a continuous chart I have it setup
that way. I want the answer to be 0:00 instead of the ##### symbols error
that it gives me. See sample below. My chart continues until 10:00 pm on
the end of and out.
IN 7:00 AM 7:15 AM 7:30 AM 7:45 AM
OUT
7:00 AM 0:00 ##### ##### #####
7:15 AM 0:15 ##### ##### #####
7:30 AM 0:30 0:15 0:00 #####
7:45 AM 0:45 0:30 0:15 0:00
8:00 AM 1:00 0:45 0:30 0:15

date calculation between two times
 

With your data in A1:E7, enter in B3:

=IF(B$1=$A3,0,$A3-B$1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Kim Shelton at PDC" wrote in
message ...
I have a chart setup to calculate the converted time between to time frames
example. The crew started at 7:00am and ended at 10:00 am. Everything
works
except with the time is started at 7:15 am and ended at 7:00 am which of
course is not possible but because it is a continuous chart I have it
setup
that way. I want the answer to be 0:00 instead of the ##### symbols error
that it gives me. See sample below. My chart continues until 10:00 pm on
the end of and out.
IN 7:00 AM 7:15 AM 7:30 AM 7:45 AM
OUT
7:00 AM 0:00 ##### ##### #####
7:15 AM 0:15 ##### ##### #####
7:30 AM 0:30 0:15 0:00 #####
7:45 AM 0:45 0:30 0:15 0:00
8:00 AM 1:00 0:45 0:30 0:15

date calculation between two times
 

You'll see #####'s in a cell if you try to show negative times (or dates) and
you're not using 1904 as your base date.

Maybe you can adjust the formula:

=if(a$2b$2,0,b$2-$a2)



Kim Shelton at PDC wrote:

I have a chart setup to calculate the converted time between to time frames
example. The crew started at 7:00am and ended at 10:00 am. Everything works
except with the time is started at 7:15 am and ended at 7:00 am which of
course is not possible but because it is a continuous chart I have it setup
that way. I want the answer to be 0:00 instead of the ##### symbols error
that it gives me. See sample below. My chart continues until 10:00 pm on
the end of and out.
IN 7:00 AM 7:15 AM 7:30 AM 7:45 AM
OUT
7:00 AM 0:00 ##### ##### #####
7:15 AM 0:15 ##### ##### #####
7:30 AM 0:30 0:15 0:00 #####
7:45 AM 0:45 0:30 0:15 0:00
8:00 AM 1:00 0:45 0:30 0:15

--

Dave Peterson

date calculation between two times
 

You haven't told us what formula you've used, but if you have =A3-C1 giving
the negative result displayed as #####, try changing the formula to
=MAX(A3-C1,0)
--
David Biddulph

"Kim Shelton at PDC" wrote in
message ...
I have a chart setup to calculate the converted time between to time frames
example. The crew started at 7:00am and ended at 10:00 am. Everything
works
except with the time is started at 7:15 am and ended at 7:00 am which of
course is not possible but because it is a continuous chart I have it
setup
that way. I want the answer to be 0:00 instead of the ##### symbols error
that it gives me. See sample below. My chart continues until 10:00 pm on
the end of and out.
IN 7:00 AM 7:15 AM 7:30 AM 7:45 AM
OUT
7:00 AM 0:00 ##### ##### #####
7:15 AM 0:15 ##### ##### #####
7:30 AM 0:30 0:15 0:00 #####
7:45 AM 0:45 0:30 0:15 0:00
8:00 AM 1:00 0:45 0:30 0:15