Two 00:00 O'clocks!

TimMead

New Member
Joined
Jul 23, 2010
Messages
4
Hi,

I have a really simple chart based on times when jobs finish. Simple stuff, until it crosses midnight. Then I get a really odd chart with Y axis 00:00 at the bottom and 00:00 further up.

I know, this is a problem of Time being a number.. but this must be a problem people have has millions of times:confused:!

<TABLE style="WIDTH: 244pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" span=2 width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Date</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 98pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=131>Arrived</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 98pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=131>SLA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40179">01-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.94652777777777775">22:43</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40180">02-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.9819444444444444">23:34</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40181">03-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.87638888888888899">21:02</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40182">04-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.98541666666666661">23:39</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40183">05-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="1.3194444444444444E-2">00:19</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40184">06-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2.9861111111111113E-2">00:43</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40185">07-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="5.5555555555555552E-2">01:20</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40186">08-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.97916666666666663">23:30</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40187">09-Jan-10</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.94444444444444453">22:40</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">01:00</TD></TR></TBODY></TABLE>

I tried tricking Excel, having two lines, one before midnight, one after. But, it still have me two 00:00 Y axis rows.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
I just tried this myself to confirm my suspicions; Formatting.

On my chart the number format was #,##0;-#,##0 and Linked to Souce, and the Scale Min & Max were 0 & 1.2 respectively. First off change the 1.2 to a 1.

I deselcted teh tick box adjacent Linked to Souce but that made no difference. Had to change to a custom Number format of [h]:mm.
 

TimMead

New Member
Joined
Jul 23, 2010
Messages
4
Hi Miles... thanks.. but not quite.. now I have one 00:00 at the top and one 24:00, this is esentially the same time. There are long lines between before and after midnight.

Would it be possible to change the center row from 12:00, to 00:00. Thus, before midnight under the line, after midnight, above.

Is this possible ?
 

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
I guess you are using a line chart then; I tried an XY and only plotted the dots.

Placing 00:00 in the centre of the axis would then give you two 12:00; one at the bottom and one at the top. As you say these are essentially the same time of day and you are back to square one.

If on the other hand rather than plot absolute time-stamps you want to show the number of minutes before/after midnight that the jobs finish then I personally would tackle that by calculating those times in another collumn. For example:

=IF(finish_time > 12:00, 24:00-finish_time,finish_time)

Then plot that data. As some of it will be negative [unless you use ABSolute()] it will plot below the horizontal axis and the rest will be above. Your Min/Max for the axis could be set to say +/- 3 hours - based on your sample data.
 
Last edited:

TimMead

New Member
Joined
Jul 23, 2010
Messages
4

ADVERTISEMENT

Hi,

Interesting!

In answer to your first thought, it would be perfectly good to have 12:00 at top and bottom, with 00:00 in the center.

Second, not sure if I translated your formula correctly (or i mis-understand the thought)

=IF(B2>TIME(12,0,0), TIME(24,0,0) - B2,B2)

This leaves me with +ve and -ve numbers.. not times ?

Thanks

Tim
 

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
Tim, although TIME(12,0,0) gives noon TIME(24,0,0) will give midnight.

Try =IF(B2>0.5, 1-B2,B2)

I had wrongly assumed that negative times were OK! even though the forumal does not return any. The above formula produces ONLY positive values (format them as [h]:mm:ss to give times).

If both positive and negative values are required then use:

=IF(B2>0.5, B2-1, B2)

however I cannot yet see how to show -ve clock values.
 

TimMead

New Member
Joined
Jul 23, 2010
Messages
4
Hi Mike,

That is really really close! The graph is good, midnight is at the center, line is correct. Altering the scale on the YAxis, can get nice hour lines.

But, as you say, the y axis labels before midnight (-ve if you will) are missing.

(shame I can't copy and paste a screen shot here)
 

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
Tim, One more option:

change the formula to

=IF(B2>0.5, B2-1, B2)*24

and Format as a number (e,g, 0.0_ ;[Red]-0.0 ).

The vertical axis will now correctly show both positive and negative times but as decimal fractions instead of hh:mm format. One deciaml place looks a good enough resolution to me.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,218
Messages
5,509,886
Members
408,760
Latest member
DoktorPhill

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top