VBA To subtract today's date from a date in column T and return a number in Column U

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
117
Office Version
  1. 2016
I hope you can help. I have a small piece of code below. The issue I am having is that I am trying to subtract today's date from the date in Column T and then return a numerical result in Column U and then continue this formula down Column U until there is no values left in column T to subtract against.


So today's date is 01/07/2018 I want to subtract this date from the date in C2 06/07/2017 giving me 185 in U2 and then continue this through column U until T has a blank cell.


The piece of code that is bugging is .Range("U" & i).Value = DateDiff("u", .Range("T" & i).Value, Date)


The error I get is run time error 5 Invalid Procedure call ur argument


Dim LastRow As Long, i As Long
With Worksheets("Allocation")
LastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
For i = 2 To LastRow
.Range("U" & i).Value = DateDiff("u", .Range("T" & i).Value, Date)
Next i
End With
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
why not?

Code:
Sub test()

 Dim LastRow As Long, i As Long
 With Worksheets("Blad1")
 LastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
 For i = 2 To LastRow
 .Range("U" & i).Value = Date - Range("T" & i).Value
 Next i
 End With
End Sub
 
Upvote 0
Thank you so much for the quick response however i am getting this in date format , need this in Number in mean in days .
 
Upvote 0
i did it , thank you so much.

Selection.NumberFormat = "General"
 
Upvote 0
Change the format of Col U to number format
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top