asked on

Chart Axis

I am having problems with the x axis on a bar chart

If the user picks Last Full Month - I want to show the data by day i.e. 01/05/2013, 02/05/2013. This works correctly

If the user picks Last Full Day I want to display the data by hour i.e. 1:00,2:00 - at the moment this does not display in the correct order i.e. displays 1:00,10:00,11:00,12:00 instead of 1:00, 2:00, 3:00 etc.

My formula is as below

if {?datechoice} = 'Last Full Day' then totext(hour({Command.sentdatetime}),2,"" ) else
if {?datechoice} in [ 'Last Full Month'] then totext(cdate({Command.sentdatetime}),'dd/MM/yy' )

Is there a way I can correct this to show it in the right order?

Using CR2008

Mike McCracken

Try

ToText ({Command.sentdatetime}),'yy/MM/dd' )

mlmcc

Mark Wilson

ASKER

Thanks for the answer

The data for day works correctly i.e. totext(cdate({Command.sentdatetime}),'dd/MM/yy' ) - this displays in the correct order 01/05,02/05 etc

The data for hour totext(hour({Command.sentdatetime}),2,"" ) - does not display in the correct order, this displays 1:00,10:00,11:00,12:00 etc. I need it to display as 1:00,2:00,3:00 etc

Mike McCracken

Try

totext(hour({Command.sentdatetime}),"00:00" )

mlmcc

Mark Wilson

ASKER

Tried the above, got the error

Bad number format string

James0628

If you just want the hour, without the minutes, but want to include ":00" for the minutes (as in your examples), you can use:

totext({Command.sentdatetime},"hh:00")

Or, to get just the hour:

totext({Command.sentdatetime},"hh")

Note that I didn't use the Hour function. ToText is extracting the hour from the datetime and formatting it.

Interestingly, ToText doesn't like a ":" in a format string for a number (like the result of the Hour function). That's why you got an error on mlmcc's suggestion. But it allows it when formatting a datetime, including "hh:00" (which gives you the hour, followed by ":00").

James

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

Yeah, that would work too. Never thought of that. :-)

James