code for date between two dates

[bambin]

Hello here,
I need some help to make my code work. I need vba to make automatically calculations bettween two periodes:
- 1st june and 1st nov
- 1st nov and 1st june
Thanks in advance for your advises.


De code looks like this:

Private Sub CommandButton1_Click()
 
Dim d As Date, t As Date, startTime1 As Date, startTime2 As Date, startTime3 As Date, Op As String, Vgem As String
d = CDate(TextBox1.Text)
d = TextBox1.Value
TextBox1.Value = Format(TextBox1.Value, "DD-MM-YYYY")
t = CDate(TextBox2.Text)
t = TextBox2.Value
TextBox2.Value = Format(TextBox2.Value, "hh:mm")
startTime1 = #1/6/2014#
startTime2 = #1/11/2014#
startTime3 = CDate("06:00")
startTime3 = CDate("20:00")
Range("b2").Value = d
Range("a2").Value = "Date"
Range("c1").Value = "Opbrengst"
Range("d1").Value = "Gemiddel dagverbruik"
If d > startTime1 And startTime2 > d Then Op = (2690 * (1 / 180))
TextBox3.Value = Op
TextBox3.Value = Format(TextBox3.Value, "0.000")
Range("c2").Value = TextBox3.Value
If d > startTime1 And startTime2 > d Then Vgem = 4120 * (1 / 180)
TextBox4.Value = Vgem
TextBox4.Value = Format(TextBox4.Value, "0.000")
Range("d2").Value = TextBox4.Value
 
End Sub

[jimatqsi]

bambin,
Welcome to Bytes.com. You'll find plenty of help here but we do ask that you follow a few simple rules when posting.

Please be sure to use tags around any programming code you post. You can do that by clicking the [CODE/] button and the typing or pasting your code between the tags.

To answer your question, look at the datediff function. It gives you a simple way to calculate days between dates. It can calculate differences in hours, months, years and so on.

You'll use something like

n=datediff("d",startTime1,startTime2)

Jim

[bambin]

Hello Jim,
Thanks for replying. Wat I need is to make vba returning de correct informations(calculations) depending on the period of the year. With the datediff function I can only calculat the diffference in yaers,month...
I use the code

If d > startTime1 And startTime2 > d Then Op = 2690 * (1 / 180) And Vgem = 4120 * (1 / 180)

But it doens't work.

[Seth Schrock]

It sounds like you need the Between() Function.

If D Between startTime1 AND startTime2 Then...

[bambin]

Hi Seth,
That' s wat I need...but there is no Between function in vba en that' s little bit frustrating

[zmbd]

bambin

1) You need to tell us which Office\Program you are using. Guessing from

Range("b2").Value = d

it appears that you are asking about an Excel workbook.

2)By, simply stating that your code "doesn't work," and appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted. Instead, please tell us what you were expecting to happen, what actually happened, and if there are errors: for each error: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred. These are the minimum requirements for posting a question of this nature.

3)

With the datediff function I can only calculate the diffference in yaers,month...

And yet you do not tell us the period that you are attempting to determine. The vba datediff() function will return Years, quarters, months, weeks, days, hours, minutes, seconds between two dates. Thus, I am at a loss as to what it is that you are attempting to do that this function will not handle for you (either in one step or two). To get the details on this function, open your VBA-Editor, type in datediff, select, and press [F1]

>Unless this is a homework problem and you're not allowed to use this function. In which case we can't do the homework for you<

4) in Excel, "between" isn't a VBA option (^_^)

[bambin]

Helo zmbd,
I' using de " Developer in Excel 2010. What I need visual basic to do is to generate/return the right calculations depending on the period of the year, when the date(d) and time(t) are filled in the " Userform" :

Dim d, t, startTime1, startTime2 As Date
Dim Op As String, Vgem As String
d= CDate(TextBox1.Text)
t=CDate(TextBox2.Text)  
startTime1=#1/5/2014# 
startTime2=#1/11/2014#
startTime3= "06:00"
startTime3= "20:00"  
If d>= startTime1 And d<= startTime2 And t>=startTime3 And t<=startTime2 Then Op= (2690*(1/180)*0.17)And Vgem= 4120*(1/18)

- Then nothing happens when I try to test and I don't get an error message
- It only return the calculation when I just fill

If d>= startTime1 Then Op= (2690*(1/180 

- My third issue is that I also wanna make it work in the future years. That's why I tried the function DateSerial maar it's didn't work wheb I filled 12-10-2016 for example
I hoop you understand my issues/problems
Kind regards.

[zmbd]

1) You still have not told us the time period you are attempting.

2) You have not explained why the Datediff() will not suit your needs.

3) Can you give us some examples of what it is you are trying to accomplish.

[bambin]

Evening zmbd,
When de user will fill a date and time, the program(vba) must automatically return de expecyed calculations. The calculations depend on the period of the year(winter or summer) en the time (day of night). In de winter the expected calculations are different than during the summer time. These results are also different the night and the day. I hope you understand what I mean.
Regards.

[zmbd]

Post#9 does help with the dates given in OP...

Winter vs. Summer (1st june thru 1st nov ;
- 1st nov and 1st june )
So return just the month part of the date and test to see if the value is between 6 and 10 or 11 and 5. I'd most likely use a select..case construct here as one can use the "to" construct in the conditional.
Be careful here with the span across the year (dec/jan) that there isn't something that should be accounted for...

day of night
Now we get into something subjective; however, one only need to setup your If..then or select..case construct to test the hour part (as it is returned in 24 hour values) for the time frame of interest.

- that's as far as I can take you for now, unless you can provide, and clearly explain, the algorithm(s) and most likely a set of example data. Otherwise I very seriously doubt that anyone here is going to be able to help you much more than this.

[bambin]

Hello zmbd,
Thanks you for your usefull advice. It works!!

[zmbd]

Yea (^_^)

Would you mind posting back your solution so that others that may be following or stumble into this thread can benfit too?

thnx