Excel GANTT Chart based on imported Dates data

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

Anonymous12223

Posted 2017-06-16T08:50:16.667

Reputation: 260

1Try DATEVALUE(M4) – Máté Juhász – 2017-06-16T09:12:51.030

My, 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.070

instead of DAY you just use 01, 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.803

What 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 as 28/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.827

1You need to understand that date format and date value are two different things. – teylyn – 2017-06-16T10:02:55.993

Answers

1

If Excel recognizes the value as a date/time value and you just want to remove the time from the date/time value, you can use this formula:

=int(M4)

Excel stores dates as whole numbers, starting from 1/1/1900, and times are decimals, i.e. 0.5 is 12 noon.

teylyn

Posted 2017-06-16T08:50:16.667

Reputation: 19 551

I don't think he already has data recognized as date, otherwise output of his formula wouldn't be 00/01/00 – Máté Juhász – 2017-06-16T09:57:49.560

I guess this is the cleanest answer. My first template is indeed a Date/Time entry – Anonymous12223 – 2017-06-16T16:07:09.077

Asked: 2017-06-16T08:50:16.667

Viewed: 131 times

Active: 2017-06-16T09:18:52.503