VBA Code to calculate CURRENT DATE - COLUMN DATE and insert the difference in Another Column

[Jabes7]

Hi Mates,

I need a Piece of VBA Code to calculate CURRENT DATE - COLUMN(A) DATE and enter the days in Column B

Tried with Date Diff Function. But Dont know how exactly to do.

Thanks in Advance

 

[JoeMo]

in B1 and copy down:

=TODAY()-A1

 

[Jabes7]

Thanks Joe,

It Works Fine like NOW()-A1 . But What i need is, Some rows in my A Column has null value. Excel Considers it as Year 2122 and formulates 41000 days in my col B. Suggest a Solution.

Thanks in Advance!

 

[Ingolf]

Hi,

Try =IF(A1="","",TODAY()-A1)

Or =IF(OR(A1="",A1=0);"",TODAY()-A1)

 

[Jabes7]

Wonderful Ingolf.

Okay the value is displayed. Now the Issue is, the value displayed as 17.93 . I need only the count of days. So, if i remove the precisions through 'format cells option' it getting rounded to 18 .

Please, Guide me how to display the value 17 alone even if its 17.99

 

[Ingolf]

Hello,

I think using the function Now () not TODAY () the result is with decimal. Anyway try

= INT (IF (OR (A1 = "", A1 = 0), "", NOW ()-A1))
or this
= FLOOR (IF (OR (A1 = "", A1 = 0), "", NOW ()-A1), 1)

 

[Jabes7]

Nicely done friend.

It works fine. Adding to that, now my requirement is if A1>Currentdate it should not calculate. I modified and used your code as

=INT(IF(OR(A!="",A1>NOW()),"",NOW()-A1))

And i used FLOOR Function. Both INT and FLOOR Function Displays ' #VALUE! ' when A1>Currentdate. Kindly fix friend!

Hello,

I think using the function Now () not TODAY () the result is with decimal. Anyway try

= INT (IF (OR (A1 = "", A1 = 0), "", NOW ()-A1))
or this
= FLOOR (IF (OR (A1 = "", A1 = 0), "", NOW ()-A1), 1)

 

[Ingolf]

Hi,

try =IF(OR(A1="",A1=0,A1>NOW()),"",INT(NOW()-A1))