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

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
18
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,873
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
18
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,873
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
18
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,873
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
18
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,119,109
Messages
5,576,159
Members
412,701
Latest member
Yong girl
Top