working with date formula

[288enzo]

My starting point for all calculations is B6

Book1
	B	C	D	E	F	G	H	I
2							Tuesday, November 30
3							#VALUE!	H2 + 2
4							#VALUE!	H3 + 5
5							#VALUE!	H4 + 2
6	11/30/2021 10:00:00 AM
Sheet1

Cell Formulas
Range	Formula
H3,H5	H3	=H2+2
H4	H4	=H3+5

Part of my macro is

test1 = Format([B6], "dddd, mmmm d")
[H2] = test1

What I'm having a difficult time with is adding days to H2 or to test1. I thought it would be as easy as
[H3] = test1 +2 or
[H3] = Range("B6") + 2 or
[H3] = Range("H2") + 2

 

[jasonb75]

H2 is not a valid date, it is a text string so you can't add days to it. Use B6 as your source and it will work just fine.

 

[288enzo]

H2 is not a valid date, it is a text string so you can't add days to it. Use B6 as your source and it will work just fine.

I had tried Range("H3") = Range("B6") + 2

I think one issue is B6 is not formatted as a date, it's text. The information is downloaded and that is how it was formatted to start with.

 

[288enzo]

Long way I'm sure, but this worked:

Range("D6").ClearContents
Range("D6").NumberFormat = "mm/dd/yyyy"
Range("D6") = Left(Range("B6"), 10)
Range("H3") = Range("D6") + 2

 

[jasonb75]

H2 is formatting B6 so it would need to be recognised as a valid date for that to work. See what this does.

Range("H3").Value = CDate(Range("B6").Value) + 2

 

[288enzo]

H2 is formatting B6 so it would need to be recognised as a valid date for that to work. See what this does.

Range("H3").Value = CDate(Range("B6").Value) + 2

That worked perfectly, thank you. I'll have to look up CDate.

 

[jasonb75]

There are several similar vba commands, CDate, CDbl, CLng, CStr, all of which convert something that looks like a certain data type but isn't into the correct data type.
CDbl converts a decimal in the form of a texts string to a valid number, Clng does the same with integers. CDate converts a text date to a real date and Cstr converts numbers to text strings.

In vba, the trick of adding to a text number / date to convert it to a proper number / date doesn't work like a formula, you have to convert it to a valid format first.
Because you had used Format on B6 in your code to change the data format, I originally thought that B6 was a valid date, but it looks as if Format (unlike worksheet formatting) will recognise a text date as valid.