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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top