Solved VBA Code to select biweekly date

January 27, 2020 at 11:39:19
Specs: Windows 7
I am working on a database to pull some contents from another sheet. I have to define the date on active sheet and need to change that date whenever I run the macro to change it to next biweekly date. for example, currently, the date is set on cell D9 "January 26, 2020" and I want to change it when I click the button to next biweekly date which is "February 9, 2020". I have created a table of all the biweekly dates in another sheet if that helps to move dates from there or else if a code can work here without the help of that table that will be wonderful.

See More: VBA Code to select biweekly date


#1
January 27, 2020 at 17:12:06
✔ Best Answer
When you say "next biweekly date", do you simply mean 14 days later?

Is it always cell D9?

If so...

Sub Biweekly()

     Range("D9") = Range("D9") + 14

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
January 27, 2020 at 18:24:58
DerbyDad03,

I know absolutely nothing about VBA, but that simple sub seems
like it couldn't possibly work. For example, does the program have
a default that when you add 14 to the date, it assumes that you want
to add 14 days, not 14 years? Years are at the end of the field.

-- Jeff, in Minneapolis


Report •

#3
January 27, 2020 at 19:14:02
If you know absolutely nothing about VBA, why would you say that "seems like it couldn't possibly work"?

Did you test it?

re: "does the program have a default that when you add 14 to the date, it assumes that you want to add 14 days, not 14 years? "

Yes, that's how it works. There's no "assumption" involved. That's how the developers choose to deal with date math.

It's a standard Excel thing. =D9+14 would work as an Excel formula, although it would need to be in a separate cell. Since ROG wants to change the data in a cell that already contains a date, he needs VBA.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
January 27, 2020 at 21:32:00
DerbyDad03 replied to Jeff Root:

> Did you test it?

No, of course not. I have no idea *how* to test it, and don't have VBA.

> Yes, that's how it works. There's no "assumption" involved. That's
> how the developers choose to deal with date math.

It amounts to an assumption. The assumption is that the programmer
knows that adding a number means adding that number of days. The
original poster didn't know, but you did. So the assumption wasn't
perfect, but it worked as soon as you came by to answer his question.

I'm curious, is there an equally simple way of incrementing the month
or the year by 14? Is there a similar default for time? HH:MM:SS ?

-- Jeff, in Minneapolis


Report •

#5
January 28, 2020 at 06:41:18
Jeff Root replied to DerbyDad03:

> No, of course not. I have no idea *how* to test it, and don't have VBA.

A simple No (without the "of course not") would have sufficed.

What version of Excel are you running? Any version since ~1993 - other than the free online version available at office.com - comes with VBA built in.

> It amounts to an assumption. The assumption is that the programmer knows that adding a number means adding that number of days.

You've changed the discussion. Let's recap what you said, with my emphasis added:

In #2 you said: "does the program have a default that when you add 14 to the date, it assumes that you want to add 14 days"

In #4 you said "The assumption is that the programmer knows that adding a number means adding that number of days"

In #2, it, grammatically speaking, refers to the program, not the programmer. That is what I responded to. The program (be it Excel or VBA) doesn't make assumptions. It just does what the developers coded it to do.

> is there an equally simple way of incrementing the month or the year by 14? Is there a similar default for time? HH:MM:SS ?

It would probably help if you reviewed the information at the link I offer below. The first part of the page explains how Excel deals with dates and times internal to the program. I think that will help you understand why adding integer values to a date "defaults" to adding "days".

Chip Pearson's "Dates and Times in Excel" Page is probably the most extensive, yet understandable, website related to Excel dates and times. Not only will you find an explanation as to how Excel internally deals with dates and times, you'll find links to all manner of date and time formulas and VBA code.

There are links for Date And Time Arithmetic and well as Worksheet Functions For Dates And Times. I believe that you will find the answers to your Month and HH:MM:SS questions within those pages.

http://www.cpearson.com/excel/datet...

Chip Pearson is well known in the Excel world and his website is a valuable resource for all things Excel and VBA related. Unfortunately, he is no longer with us.

Enjoy your journey.

message edited by DerbyDad03


Report •

#6
January 28, 2020 at 06:55:28
Thank you! it is working.

message edited by ROG


Report •

#7
January 28, 2020 at 07:09:02
Glad to be of assistance. Let us know if there is anything else that you need.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#8
January 28, 2020 at 07:31:17
Thank you! Appreciate it.

Report •

#9
January 28, 2020 at 11:32:28
DerbyDad replied:

> What version of Excel are you running?

I recently had MS Office installed for a while to complete a project
that was started by someone else in MS Word, but it turned out to
be a one-month trial thing, so I uninstalled it after the project was
done. I didn't get a chance to play with any other parts of Office.
I did use Excel once in the late 1990s, but never did anything with
it like the OP is doing.

> Any version since ~1993 - other than the free online version
> available at office.com - comes with VBA built in.

Ah! I thought I saw it somewhere.

I completely agree with your analysis of my posts, but the assumption
was built into the program. The assumption is that the user knows
that adding a number adds to the date. The program assumes that
a number added is added to the date. To be clear, by "programmer"
I meant the user, such as the original poster here.

Thanks for the info. My interest might stem from writing programs in
other versions of BASIC years ago that did similar kinds of things.
For example, being able to enter data in either kilograms or pounds
and getting output in either cubic meters or cubic feet.

-- Jeff, in Minneapolis


Report •

#10
January 28, 2020 at 15:01:34
We could go down a rat hole real quick here, but I just can't let that slide.

re: "The program assumes that a number added is added to the date."

A program doesn't (can't) assume anything. Since you wrote code in the past, you must know that. You know that the program is a bunch of 1's and 0's inside a piece of electronics. To be more precise, deeper down, it's just a bunch of electrical signals running through billions of circuits.

I doubt that any BASIC program you wrote made "assumptions". I'll bet that you had to tell the program every single thing you wanted it to do. Sure, some folks made it easier for you to tell the program what to do by providing a high level language, but that was eventually converted into machine code and eventually into electrical 1's and 0's, Highs and Lows, >2.7V and <.8V, etc. No thought process, no "guessing with little or no evidence" i.e. no assumptions.


message edited by DerbyDad03


Report •

#11
January 30, 2020 at 05:00:30
I think you are interpreting the idea of an assumption as a
cognitive thing. I'm not.

I don't see any problem with calling what the program does
"an assumption". The way the wheel on my computer
mouse works assumes that when I push the wheel forward,
I want the screen to scroll down. I recently used an Apple
computer on which pushing the wheel forward made the
screen scroll up. It makes the opposite assumption. Or to
put it in a way that might not grate on you quite so much,
the designers made the opposite assumption about what
most users would prefer. Adding a number to a date in an
Excel cell with the given format could easily do something
other than incrementing the date. Since the year was at the
end of the field, my first guess was that the year would be
incremented. I use the Japanese standard format for dates
when I have that option, so I see "2020-01-30" in the corner
of my screen. My second guess was that some kind of
modulo operator would have to be used to come up with a
sensible date. Then I realized that Excel might assume an
input number is a date, rather than just a number.

I think designing the user interface is by far the most fun
and usually the easiest part of making a computer program.
When Microsoft came out with Windows, my reaction as a
programmer was "We take care of the fun, easy part for you,
so you can concentrate on the tedious drudgework."

-- Jeff, in Minneapolis

message edited by Jeff Root


Report •

#12
January 30, 2020 at 06:34:33
IMO...

This statement uses of the concept of an assumption correctly: "the designers made the opposite assumption"

This statement does not: "the way the wheel on my computer mouse works assumes"

re: "'I think you are interpreting the idea of an assumption as a cognitive thing. I'm not."

If you can provide a definition of "assumption" that indicates that it is not a cognitive thing - by definition - I may come over to your side. Every definition I can find uses words like "idea", "believe", "willingness to accept". While those are not necessarily "cognitive traits", they are all human traits. They are all thought processes. A program or (a mouse wheel) doesn't have ideas, believes, thoughts or a will and is therefore unable to make an assumption.

message edited by DerbyDad03


Report •

Ask Question