VBA - Greater than or equal to not working with dates.

[RockandGrohl]

Hopefully a quick one. I'm referencing a date (31/12/2018) as a variable "date1"

I'm then going onto another sheet and saying if the value in B is >= date1 and <= date2 then do this.

When I set it to 31/12/2018, even though this cell is less than the value in B (which is 07/01/2019), it doesn't work. However, if I change date1 to be 01/01/2019 it does work.

Somehow 01/01/2019 is valid as being less than 07/01/2019, but 31/12/2018 isn't working.

Any ideas?

Thanks.

 

[shg]

Declare those variables as type Date and post the code that errors.

 

[RockandGrohl]

Declare those variables as type Date and post the code that errors.

Hi, I have it declared as such:

date1 = Int(CDbl(DateValue("31/12/2018")))

Just to be clear, when I set it to the following:

date1 = Int(CDbl(DateValue("01/01/2019")))

It seems to work perfectly.

Thanks.

 

[RockandGrohl]

date1 = DateValue("31/12/2018")
date2 = DateValue("31/12/2019")

If Cells(ActiveCell.Row, "B").Value >= date1 And Cells(ActiveCell.Row, "B").Value <= date2 Then


Above is how the dates are declared, and then where the if statement is determined.

Currently, it's looking at a spreadsheet where the value in "B" is "07/01/2019" which is greater than or equal to 31/12/2018 and less than or equal to 31/12/2019. This should work, unless I need to declare the cells(activecell.row, "B").value as a date too, in which case how do I do that?

Thanks.

 

[RockandGrohl]

I think I can fix this, but need to know how.

There's a part in my code that says "wcdate = date1"

This is showing an incorrect "week commencing" date of 1st Jan, this then has +7 added to it later, so 1st Jan, 8th Jan, 15th Jan. Of course, this year, it is now 31st December 2018, then 7th Jan, 14th Jan etc.


How do I just subtract one from the "wcdate"?

I need date1 to be set for 01/01/2019 as that's the beginning of the year, but tying the WC date into 1st Jan is producing calculation errors.

Thanks!

 

[Joe4]

How do I just subtract one from the "wcdate"?

Excel stores dates as a numbers, specifically the number of days since 1/0/1900. So one day is just equal 1.
So, if "wcdate" is a valid date, to subtract one from it you would just do:

wcdate - 1

 

[RockandGrohl]

Excel stores dates as a numbers, specifically the number of days since 1/0/1900. So one day is just equal 1.
So, if "wcdate" is a valid date, to subtract one from it you would just do:

wcdate - 1

I did try that, however it came up with some sort of VBA error. Instead, I found where it was doing a sumif where the date for instance was in Cell H3, I simply did "H3-1" to get it to start using the correct date.

 

[Joe4]

I did try that, however it came up with some sort of VBA error.

That would imply that wcdate is not a valid date. If it was, the subtraction would work.

If you are having trouble figuring it out, please all your VBA code.

 

[RockandGrohl]

No problem.

It's long code and some may or may not be confidential so I've pasted the relevant bits.

Here's the Declarations

Sub comrun()

Dim j As Worksheet, o As Worksheet, temp As Worksheet, lastrow As Long, af1 As Workbook, wbk As Workbook, date1 As String, date2 As String, runtype As String, r As Worksheet, jc As Worksheet, oc As Worksheet, wcdate As String, wno As String

Then we declare date1 and date2 as Date Values

date1 = DateValue("01/01/2019")
date2 = DateValue("31/12/2019")

For x = 1 To 2    Do Until Cells(ActiveCell.Row, "A").Value = ""
        If Cells(ActiveCell.Row, "B").Value >= date1 And Cells(ActiveCell.Row, "B").Value <= date2 Then
        ttyp = Cells(ActiveCell.Row, "J").Value

Above is where the issues begin. When I leave this as 01/01/2019 it works, but when I change to 31/12/2018 (which is the Monday of the week that starts January off in 2019) it stops registering.

        wcdate = Int(CDbl(DateValue(Cells(ActiveCell.Row, "B").Value)))

This is now saying that the date in B is the week commencing date.

        Cells(ActiveCell.Row, "I").Value = wcdate

Then taking that wcdate and pasting it in I.

wcdate = DateValue("31/12/2018")

This WAS wcdate = date1 but I changed it manually to be 31/12/2018 which seemed to work.

 

[Joe4]

You are actually NOT declaring those variables as dates.
Look at your DIM line at the top.

... [COLOR=#333333]date1 As [/COLOR][COLOR=#ff0000]String[/COLOR][COLOR=#333333], date2 As [/COLOR][COLOR=#ff0000]String[/COLOR][COLOR=#333333] ...[/COLOR]

Change that to "As Date" for both of them.