Date Format

[solti]

In the following code when setting the variable "Timeline", it's correct, for example "2017-03-10" but in R1C1 formula it's displayed as "2017-3-10". I changed Timeline in R1C1 formula to Format(Timeline,, "yyyy-mm-dd").No effect. Do you have an idea what I am doing wrong?

Dim Timeline As Date 

With PlikHarmonogram.Sheets(1) 
Timeline = Application.WorksheetFunction.Min(.Columns("E")) - 60 
End With            
        
.Cells(i, j).FormulaR1C1 = _ 
"=IF(AND(" & Timeline & "<=R4C," & Timeline  & " >=R4C[-1])," & _ 
"VLOOKUP(RC2," & "'[" & PlikBudżet.Name & "]" & PlikBudżet.Sheets(1).Name & "'!R" & WierszKosztyPensji_Budzet + 2 & _ 
"C" & OstKol_Budzet & ":R" & WierszKosztyPensji_Budzet + 2 & "C" & OstKol_Budzet + 2 & ",3,0),"""")"

 

[Jonmo1]

Remember, VBA isn't creating a formula.
VBA is only creating a Text String.
The act of putting that text string in a cell makes it a formula.

Timeline is literally being entered as 2017-03-10
So this
"=IF(AND(" & Timeline & "<=R4C," & Timeline & " >=R4C[-1])," & _
is translated as this
"=IF(AND(" & 2017-03-10 & "<=R4C," & 2017-03-10 & " >=R4C[-1])," & _
"=IF(AND(2017-03-10<=R4C,2017-03-10>=R4C[-1])," & _

At this point, now being a formula in the cell (no longer VBA)
2017-03-10 are NOT being read as a DATE.
It is read as 2017 minus 03 minus 10 (this is why 03 changes to 3, because real numbers don't have leading spaces)

You need that to end up looking like
"=IF(AND("2017-03-10"+0<=R4C,"2017-03-10"+0>=R4C[-1])," & _
Having it in Quotes and Adding 0 makes it convert to an actual date.

Try
"=IF(AND(""" & Timeline & """+0<=R4C,""" & Timeline & """+0 >=R4C[-1])," & _

 

[solti]

Remember, VBA isn't creating a formula.
VBA is only creating a Text String.
The act of putting that text string in a cell makes it a formula.

Timeline is literally being entered as 2017-03-10
So this
"=IF(AND(" & Timeline & "<=R4C," & Timeline & " >=R4C[-1])," & _
is translated as this
"=IF(AND(" & 2017-03-10 & "<=R4C," & 2017-03-10 & " >=R4C[-1])," & _
"=IF(AND(2017-03-10<=R4C,2017-03-10>=R4C[-1])," & _

At this point, now being a formula in the cell (no longer VBA)
2017-03-10 are NOT being read as a DATE.
It is read as 2017 minus 03 minus 10 (this is why 03 changes to 3, because real numbers don't have leading spaces)

You need that to end up looking like
"=IF(AND("2017-03-10"+0<=R4C,"2017-03-10"+0>=R4C[-1])," & _
Having it in Quotes and Adding 0 makes it convert to an actual date.

Try
"=IF(AND(""" & Timeline & """+0<=R4C,""" & Timeline & """+0 >=R4C[-1])," & _

Thanks a LOT!

 

[Jonmo1]

You're welcome.