# VBA - Calculate interval between two dates

#### OldAndWeak182

##### New Member
Hi guys,
Would you be kind enough to help me out? I hit my head all day but finally gave up.
So...

first example:
In A1 I have 01/01/2021 and in A2 I have 05/01/2021 (european date).
In VBA how can I do to calculate the range "in between" and write for example in B1 01/01/2021, in B2 02/01/2021, in B3 03/01/2021 etc?

second example linked to the first:
Same scenario as before.
This time, however, I would like to insert variables: from B1 you must always write the range of dates but skip those that have been prescribed in another specific range X of cells, better still skip them based on the color of the cell of the X range (example 02/01/ 2021 is colored red so don't write it).

Good evening

#### Marc L

##### Well-known Member
This was the Excel way …​
Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​
VBA Code:
Sub Demo0()
Dim E&, L&, V, F%, R&
E = 1
L = [E1].End(xlDown).Row
For V = [A1].Value To [A2].Value
F = 1
For E = E To L
If Cells(E, 5).Value >= V Then
If Cells(E, 5).Value = V Then If Cells(E, 5).Interior.Color = vbRed Then F = 0
E = E + 1
Exit For
End If
Next
If F Then
R = R + 1
Cells(R, 2).Value = V
End If
Next
End Sub

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### OldAndWeak182

##### New Member
This was the Excel way …​
Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​
VBA Code:
Sub Demo0()
Dim E&, L&, V, F%, R&
E = 1
L = [E1].End(xlDown).Row
For V = [A1].Value To [A2].Value
F = 1
For E = E To L
If Cells(E, 5).Value >= V Then
If Cells(E, 5).Value = V Then If Cells(E, 5).Interior.Color = vbRed Then F = 0
E = E + 1
Exit For
End If
Next
If F Then
R = R + 1
Cells(R, 2).Value = V
End If
Next
End Sub

what's the difference? the second is optimized better?

#### OldAndWeak182

##### New Member
This is what I am trying to achieve at the end of all experiments:
* I have a userform where I enter the data
* when I execute the code I have to write in the calendar the two variables indicated according to the date range, if I find one cell busy I jump to the next one, if I find it busy I jump to the last one, if I find it busy I return an error (referring to X,Y,Z)
* if the dates indicated are red then I skip the day and go to the next day.
Let me explain better with the example in the image: I insert from 01/04/2021 to 06/04/2021 but I write in the calendar only in the "Y" column.
Alone I was able to do everything only if the start and end date are the same, if instead I have a wider range I don't know how to proceed.
How would you set up the whole macro?

#### Marc L

##### Well-known Member
what's the difference? the second is optimized better?
It was just a demonstration according to your post #2 attempt …​
My Demo0 well works with your sample but is not optimized for all cases​
where the codeline E = E + 1 can create some glitch so the revised version is​
VBA Code:
Sub Demo0r()
Dim E&, L&, V, F%, R&
E = 1
L = [E1].End(xlDown).Row
For V = [A1].Value To [A2].Value
F = 1
For E = E To L
If Cells(E, 5).Value >= V Then
If Cells(E, 5).Value = V Then
If Cells(E, 5).Interior.Color = vbRed Then F = 0
E = E + 1
End If
Exit For
End If
Next
If F Then
R = R + 1
Cells(R, 2).Value = V
End If
Next
End Sub

#### Marc L

##### Well-known Member

How would you set up the whole macro?
Elaborate what the code should do …​

#### OldAndWeak182

##### New Member
Elaborate what the code should do …​
Everything I wrote, I set a loooong macro to do the whole thing so I would like to read and learn how someone who is really capable to write a code do the task

#### OldAndWeak182

##### New Member

the most immediate thing I have to do:

VBA Code:
If Cells(E, 5).Value >= V Then
If Cells(E, 5).Value = V Then
If Cells(E, 5).Interior.Color = vbRed Then F = 0
E = E + 1
End If

I need to modify this step so that the dates to be excluded are on many different ranges. Should a cycle be set? how?

#### Marc L

##### Well-known Member
Or load first those dates in a single array in order to use it to compare …​

#### OldAndWeak182

##### New Member
This was the Excel way …​
Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​
VBA Code:
Sub Demo0()
Dim E&, L&, V, F%, R&
E = 1
L = [E1].End(xlDown).Row
For V = [A1].Value To [A2].Value
F = 1
For E = E To L
If Cells(E, 5).Value >= V Then
If Cells(E, 5).Value = V Then If Cells(E, 5).Interior.Color = vbRed Then F = 0
E = E + 1
Exit For
End If
Next
If F Then
R = R + 1
Cells(R, 2).Value = V
End If
Next
End Sub
For some reason I have issue with formatting cells as dates.
Is possible to do the the whole things formatting cells as text?

#### Marc L

##### Well-known Member
For some reason it should be difficult as a text can't be a date (numeric) so it depends on if Excel can understand the text as a valid date.​
But as formatting a cell is at kid level so the easier is to directly work with dates rather than texts …​

Replies
2
Views
53
Replies
6
Views
163
Replies
3
Views
209
Replies
2
Views
238
Replies
1
Views
251

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,968
Messages
5,767,375
Members
425,409
Latest member
Whatisanexcel

### 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.

### Which adblocker are you using?

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

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