6 Replies Latest reply on Dec 26, 2017 7:13 PM by Ritesh Bisht

# I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

date & time
Mar 7,2017 1:37:20
Mar 7,2017 1:47:55
Mar 13,2017 10:20:16
Mar 13,2017 9:30:20
Mar 13,2017  4:30:00
Mar 17,2017 12:01:20

This is the sample date, these date in string, I want to calculate the time difference and day difference, show day difference in Tooltip or axis, time difference in size. Here i have attached the sample data and my twbx file , i have calculate time difference in excel, that is no problem . Please anybody to help me

• ###### 1. Re: I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

Hello Elakiya,

Do you mean like below ? Diff in days ?

I used this field -->Time Diff in Days= (min([Date Range Date & time])- LOOKUP(min([Date Range Date & time]),-1))

If you want diff in Hours then  (min([Date Range Date & time])- LOOKUP(min([Date Range Date & time]),-1))*24

PFA,

Thanks,

Ritesh

• ###### 2. Re: I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

Hi Elakiya,

Find my approach as reference below and stored in attached workbook version 10.4 located in the original thread

1. D0. find 3nd " ": FINDNTH([Date Range Date & time],' ',3)

2. D1. find 3nd "," : FINDNTH([Date Range Date & time],',',1)

3. D2. Year Notation: int(mid([Date Range Date & time],[D1. find 3nd "," ]+2,4))

4. D3. Month Notation:  mid([Date Range Date & time],1,3)

5. D4. Month converter:

if [D3. Month Notation]='Jan' then 1

elseif [D3. Month Notation]='Feb' then 2

elseif [D3. Month Notation]='Mar' then 3

elseif [D3. Month Notation]='Apr' then 4

elseif [D3. Month Notation]='May' then 5

elseif [D3. Month Notation]='Jun' then 6

elseif [D3. Month Notation]='Jul' then 7

elseif [D3. Month Notation]='Aug' then 8

elseif [D3. Month Notation]='Sep' then 9

elseif [D3. Month Notation]='Oct' then 10

elseif [D3. Month Notation]='Nov' then 11

elseif [D3. Month Notation]='Dec' then 12

end

6. D5. Day Notation: int(mid([Date Range Date & time],5,((5-[D1. find 3nd "," ])*-1)))

7. D6. Date Notation: MAKEDATE([D2. Year Notation],[D4. Month converter],[D5. Day Notation])

8. D7. Time Notation: DATEPARSE ( " hh:mm:ss a", mid([Date Range Date & time],[D0. find 3nd " "],12) )

9. D8. Time: DATEPARSE ( "yyyy-MM-dd HH:mm:ss", str([D7. Time Notation]) )

'second', DATEPART('second', [D8. Time]), [D6. Date Notation])))

11. P1. Lookup date time: lookup(attr([D9. Date Time]),-1)

12. P2. Delta Day: datediff('day',[P1. Lookup date time],attr([D6. Date Notation]))

13. P3. Delta minute: datediff('minute',[P1. Lookup date time],attr([D9. Date Time]))

14 Drag required objects to the indicated locations.

Regards,

Norbert

1 of 1 people found this helpful
• ###### 3. Re: I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

Hi Elakiya,

I am not sure about your image of final product, but here is one idea.

All of table calc need to be set as same.

If you need string => day,time conversion,

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

Thanks for your suggestions . But , I need output in the above mentioned format. Is there any possibilities?

Thanks,

Elakiya

1 of 1 people found this helpful
• ###### 5. Re: I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

I guess Date should be discrete here , hence can't join the rectangles there.

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 6. Re: I want show the time difference in size and day difference in Tooltip or axis in gantt chart, but in my data dates are single column

Please note that Time Diff is in Mins not Hour (It is just showing up since I used the same variable to test for Hour as well )

1 of 1 people found this helpful