0
I'm building a GANTT Chart on Excel.
I'm basically importing Data from a source that has dates formatted like 28/04/2017 08:00:00
and I want to use a Stacked Horizontal Bar Diagram for the GANTT Chart
The Stacked Horizontal Bar diagram only seem to accept dates formatted as 28/04/2017
. So I was trying to use a formula to convert the first date template in to the second one. I goes as follows (translated from French) :
=IF( ISEMPTY(M4);"";
CONCATENATE("01/";
IF(LEN(MONTH(M4)) = 1; CONCATENATE(0; MONTH(M4)); // To Have 01, 02, ...
MONTH(M4)); "/";
YEAR(M4)))
However, the Data in the Stacked Chart begins at the Date 00/01/00, just as if the formula's output was not a date.
How could I trick my GANTT Chart into considering the output of my formula as a date ? Note that the Data Type of my fomula is set to Date
1Try
DATEVALUE(M4)
– Máté Juhász – 2017-06-16T09:12:51.030My, my ... it works. Thank you ... However, I then check out what's available and I think I'd rather use Date(Month(M4);YEAR(M4);DAY(M4)). It uses only one function rather than 2 Would you consider building a response with that ? – Anonymous12223 – 2017-06-16T09:19:50.187
The Date() function has the syntax
Date(Year,Month,Day)
, so if you feed it the month first, it will not show the correct result. – teylyn – 2017-06-16T09:21:20.070instead of
DAY
you just use01
, is it intentional to convert each day to first day of month? Also the output of your formula seems to be a date, but in reality its a text, is that intentional? – Máté Juhász – 2017-06-16T09:55:36.803What is the type of your input date? (does your data changes if you change it's number format to e.g. "number")? – Máté Juhász – 2017-06-16T09:56:53.693
@xhaltar What is your computer's normal date format? I don't understand how "dates formatted like
28/04/2017 08:00:00
" is different from "a date formatted as28/04/2017
" . The only difference I see is that one date has the time and the other date does not have the time. Is that the issue or not? What is the problem here? Date format or underlying value????? – teylyn – 2017-06-16T10:02:12.8271You need to understand that date format and date value are two different things. – teylyn – 2017-06-16T10:02:55.993