By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,297 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,297 IT Pros & Developers. It's quick & easy.

code for date between two dates

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2.  
  3. Dim d As Date, t As Date, startTime1 As Date, startTime2 As Date, startTime3 As Date, Op As String, Vgem As String
  4. d = CDate(TextBox1.Text)
  5. d = TextBox1.Value
  6. TextBox1.Value = Format(TextBox1.Value, "DD-MM-YYYY")
  7. t = CDate(TextBox2.Text)
  8. t = TextBox2.Value
  9. TextBox2.Value = Format(TextBox2.Value, "hh:mm")
  10. startTime1 = #1/6/2014#
  11. startTime2 = #1/11/2014#
  12. startTime3 = CDate("06:00")
  13. startTime3 = CDate("20:00")
  14. Range("b2").Value = d
  15. Range("a2").Value = "Date"
  16. Range("c1").Value = "Opbrengst"
  17. Range("d1").Value = "Gemiddel dagverbruik"
  18. If d > startTime1 And startTime2 > d Then Op = (2690 * (1 / 180))
  19. TextBox3.Value = Op
  20. TextBox3.Value = Format(TextBox3.Value, "0.000")
  21. Range("c2").Value = TextBox3.Value
  22. If d > startTime1 And startTime2 > d Then Vgem = 4120 * (1 / 180)
  23. TextBox4.Value = Vgem
  24. TextBox4.Value = Format(TextBox4.Value, "0.000")
  25. Range("d2").Value = TextBox4.Value
  26.  
  27. End Sub
Mar 25 '14 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 1,240
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
Expand|Select|Wrap|Line Numbers
  1. n=datediff("d",startTime1,startTime2)
Jim
Mar 25 '14 #2

P: 6
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
Expand|Select|Wrap|Line Numbers
  1. If d > startTime1 And startTime2 > d Then Op = 2690 * (1 / 180) And Vgem = 4120 * (1 / 180)
But it doens't work.
Mar 25 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
It sounds like you need the Between() Function.
Expand|Select|Wrap|Line Numbers
  1. If D Between startTime1 AND startTime2 Then...
Mar 25 '14 #4

P: 6
Hi Seth,
That' s wat I need...but there is no Between function in vba en that' s little bit frustrating
Mar 25 '14 #5

zmbd
Expert Mod 5K+
P: 5,397
bambin

1) You need to tell us which Office\Program you are using. Guessing from
Expand|Select|Wrap|Line Numbers
  1. 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 (^_^)
Mar 25 '14 #6

P: 6
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" :
Expand|Select|Wrap|Line Numbers
  1. Dim d, t, startTime1, startTime2 As Date
  2. Dim Op As String, Vgem As String
  3. d= CDate(TextBox1.Text)
  4. t=CDate(TextBox2.Text)  
  5. startTime1=#1/5/2014# 
  6. startTime2=#1/11/2014#
  7. startTime3= "06:00"
  8. startTime3= "20:00"  
  9. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 25 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
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.
Mar 25 '14 #8

P: 6
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.
Mar 25 '14 #9

zmbd
Expert Mod 5K+
P: 5,397
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.
Mar 25 '14 #10

P: 6
Hello zmbd,
Thanks you for your usefull advice. It works!!
Mar 27 '14 #11

zmbd
Expert Mod 5K+
P: 5,397
Yea (^_^)

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

thnx
Mar 27 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.