date and time is confusing me 1

[sf123]

I am writing a program where the user enters two dates and two times. e.g. 10/23/02 5:00 AM and 11/24/02 3:00 PM.
I want to take the two dates and times enterd, figure out the time interval and divide the time into 4 segments. I need to then run a query on each time segment.
I am having a lot of trouble coding this. I have changed the time to army time ( 24 hours).
Any ideas?
Thanks!

 

[xlbo]

what exactly is the problem ????
more specific please Rgds
~Geoff~

 

[sf123]

I know how to get time interval but using datediff but I have to divide it into 4 equal parts and then reformat it into 4 dates in order to do a query to get the data that falls in between 4 specific times.

eg:
begin date is 1/24/02 03:00
end date is 1/25/02 07:00
so the time difference is 28 hrs meaning that each of my four time segments are 7 hrs (28/4). Now I want to do four queries. Find data between 3 to 10 etc. How do I add 7(var is a long) to the date 1/24/02 03:00? And what would I do if it 80 hrs so I need to reformat the date, not just the time?
Is my question clear?
Thanks!

 

[VulcanJedi]

I think this is what you want? You can obviously apply to a userform or soemthing else instead but I think this can help you understand /demonstrates?
Also if you need, you can use Datepart(date) (see legit syntax) but if you have 1/25/02 9pm it returns 21 hours.
Try:

Public Sub ss()
Dim a, b, c As Date

a = Cells(2, 1)
b = Cells(1, 1)
d = DateDiff("h", b, a)
d = d / 4
For x = 1 To 4

Cells(x, 3) = a + (d * x / 24)

Next x
MsgBox ("# of hrs different = " & d)

End Sub

Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!

 

[xlbo]

Time is merely the decimal portion of a date so no worries about adding times to dates
If your start date is 1/24/02 03:00 and you want to add 7 hours, use
1/24/02 03:00 + (7/24)

For 80 hours, it would be

1/24/02 03:00 + 80/24)

this works as dates are stored as serial numbers and increment by 1 each day so today being 24/10/02, the serial number is 37553. The serial number for tomorrow is 37554
Therefore, as there are 24 hours in a day, to get the value of 1 hour, divide 1 by 24

60 minutes in an hour therefore value of 1 minute = 1/(24*60)

or 1/(1440)

etc etc
(for seconds, use 1/86400)

To split into 4 therefore, based on 7 hour spans
stDateTime = 1/24/02 03:00
sliceOne = stDateTime + 7/24
sliceTwo = sliceOne +7/24
sliceThree = sliceTwo + 7/24
sliceFour = sliceThree + 7/24
then you take data between stDateTime and sliceOne
sliceOne and sliceTwo
sliceTwo and sliceThree
etc etc

tranpkp's answer is very nice but I thought I'd explain a bit how it works and show how you might create the variables in code

HTH
Rgds
~Geoff~

 

[VulcanJedi]

Thanks Geoff! Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!