VBA - Calculate interval between two dates

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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 :coffee:
 

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
This thing is driving me crazy: the dates are entered via two simple textboxes in a userbox.
I asked you if it could work on text because for some absurd reason the dates are entered in the US mm / dd / yyyy format and not the European dd / mm / yyyy format.
I am literally going crazy, I tried to insert different conditions to get the result but nothing seems to work.
How can I do?

Here's is the example to try: Gofile - Free file sharing and storage platform
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
As VBA is always in US format whatever the local version so try the cell property Formula to allocate an US 'text date' …​
 

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
As VBA is always in US format whatever the local version so try the cell property Formula to allocate an US 'text date' …​

I tried to indicate the format "dd mm yyyy" but it doesn't work. Could you give me an example please?
the macro works entirely, but returns the wrong results because the date is in the wrong format ... :|
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
{Cell object}.Formula = {US text date}​
Replace each part between { } with the appropriate …​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
So you made an error in the replacement as it well works on my side since last century …​
 

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top