How to adjust date axis in excel to range from mid day to mid day

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
28
Office Version
  1. 2010
Platform
  1. Windows
In the chart below time values 2 and 3 are 21:46 and 00:10 and should be displayed around a middling value. I would like to set the y axis minimum and maximum to something like 19:00 and 05:00 the next day but cannot make excel do it. It seems you cannot set axis maximum at a lower value than the axis minimum. I even tried setting the minimum as a negative value which didn't work. (I have it as a line chart because it is more clear how the points are related than a scatter plot). Any ideas much appreciated. Failing a proper solution I wonder if I could make it work by a adding a false date - pushing all the data before 09:36 into the next day, but don't know how to make a date time number format like dd:hh:mm work ? Plus it is laborious as I would have to type in all the false date data.

anomalies.png
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Any ideas much appreciated. Failing a proper solution I wonder if I could make it work by a adding a false date - pushing all the data before 09:36 into the next day, but don't know how to make a date time number format like dd:hh:mm work ? Plus it is laborious as I would have to type in all the false date data.

Just add 1 to the times before for ex.: 9:00. You can do that in an auxiliary column with a simple if formula.
 

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
28
Office Version
  1. 2010
Platform
  1. Windows
Just add 1 to the times before for ex.: 9:00. You can do that in an auxiliary column with a simple if formula.
Thanks, did you mean 1:9:00 because I tried that and it just made the lower values all as a flat line, everything else the same profile.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Thanks, did you mean 1:9:00 because I tried that and it just made the lower values all as a flat line, everything else the same profile.
No. I meant 1, 1 unit which in excel time means 1 day.

For ex., to add 1 for times before 21h00 means that for ex. 1:20 will become 1:20 of the next day.

Try this example.
In column B you have your source values.
In column C you get the same times but for times before 21:00 they are the same time on next day.

In C2:

=B2+IF(B2<TIME(21,0,0),1)

Copy down


Book1
ABCD
1
201/01/202023:0023:00
302/01/202023:4523:45
403/01/20202:342:34
504/01/202022:3022:30
605/01/202022:4522:45
706/01/20203:003:00
807/01/202023:3023:30
9
Sheet1



See the chart using values in column B

1605042576953.png


and the chart using values in column C


1605042605322.png
 
Solution

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
28
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

No. I meant 1, 1 unit which in excel time means 1 day.

For ex., to add 1 for times before 21h00 means that for ex. 1:20 will become 1:20 of the next day.

Try this example.
In column B you have your source values.
In column C you get the same times but for times before 21:00 they are the same time on next day.

In C2:

=B2+IF(B2<TIME(21,0,0),1)

Copy down


Book1
ABCD
1
201/01/202023:0023:00
302/01/202023:4523:45
403/01/20202:342:34
504/01/202022:3022:30
605/01/202022:4522:45
706/01/20203:003:00
807/01/202023:3023:30
9
Sheet1



See the chart using values in column B

View attachment 25843

and the chart using values in column C


View attachment 25844
Thanks, thats a really brilliant improvement. I still have a big space below the plot that I cannot get rid of but you have been a great help.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
I still have a big space below the plot that I cannot get rid of
The space below depends on the minimum value in the Y axis.

For ex., this example has 12:00 as the minimum value (which in number value is 0.5, half the day)

Just set it to whatever you want.


1605049998938.png
 

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
28
Office Version
  1. 2010
Platform
  1. Windows
That's really floored me. I have always assumed that formatting the axis minimum and maximum related to the actual values in the chart and that is what I have altered in the past. Now I am typing in a number related to proportion and it works perfectly. I wonder how excel knows which I am doing?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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
Top