Insert Date Difference

[sadasivan]

Hi Team
How can I insert date difference in " C Column "

"A column" blank cell treated as the same date of above cell

at the end of the march month date difference sum = 364/365 depend on year

 

[bosco_yip]

Hi,

1] Your existing Col A "Date" are text value, try to convert them to numeric value by :

Select A2:A415 >> Text To Column, choose "Delimited" >> Next >> Next >> Finish

2] In D3, copied down :

=IF((A3=LOOKUP(9^9,A:A))+(A3<>"")*(A4<>""),A3-LOOKUP(9^9,A$2:A2),IF((A3="")*(A4<>""),LOOKUP(9^9,A$2:A3)-LOOKUP(9^9,A$2:INDEX(A$2:A2,MATCH(2,INDEX(1/(D$2:D2<>""),0)))),""))

3] In D1 :

=SUM(D2:D415)

Regards
Bosco

 

[sadasivan]

Hi,

1] Your existing Col A "Date" are text value, try to convert them to numeric value by :

Select A2:A415 >> Text To Column, choose "Delimited" >> Next >> Next >> Finish

2] In D3, copied down :

=IF((A3=LOOKUP(9^9,A:A))+(A3<>"")*(A4<>""),A3-LOOKUP(9^9,A$2:A2),IF((A3="")*(A4<>""),LOOKUP(9^9,A$2:A3)-LOOKUP(9^9,A$2:INDEX(A$2:A2,MATCH(2,INDEX(1/(D$2:D2<>""),0)))),""))

3] In D1 :

=SUM(D2:D415)

Regards
Bosco

Thank you Thank you Thank you

Bosco

 

[sadasivan]

Hi Bosco
can fill date of blank cell in "A column " and date difference

 

[bosco_yip]

Hi Bosco
can fill date of blank cell in "A column " and date difference

I3, copied down :

=IF(F3=F4,"",IF((F3=F2)*(F3<>F4)+(F3="")*(F4<>""),LOOKUP(9^9,F$2:F3)-LOOKUP(9^9,F$2:INDEX(F$2:F2,MATCH(2,INDEX(1/(I$2:I2<>""),0)))),IF((F3=LOOKUP(9^9,F:F))+(F3<>"")*(F4<>""),F3-LOOKUP(9^9,F$2:F2),"")))

Regards
Bosco

 

[sadasivan]

need to fill down date column which is blank in "column A"

 

[bosco_yip]

need to fill down date column which is blank in "column A"

In E2, copied down :

=LOOKUP(9^9,A$1:A2)

Regards
Bosco