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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Declare those variables as type Date and post the code that errors.
 

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
wcdate - 1
 

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows
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

Code:
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

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


Code:
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.

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

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

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

Then taking that wcdate and pasting it in I.

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

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
You are actually NOT declaring those variables as dates.
Look at your DIM line at the top.
Code:
... [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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top