Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date and Time Comparison

Status
Not open for further replies.

mcelligott

Programmer
Apr 17, 2002
135
US
I am attempting to refine/re-build an if statement in excel VBA. I have not done a lot of date and time comparisons.

When the user clicks the button, Excel need to evaluate the following:

Note: the cell F2 contains a date only, not a time.

Is the current date & time between 2330 hours on F2 date-1 and 0730 hours on F2 date.

(ex: F2 = 4/8/2010, is the current date and time between 2330 hours 4/7/2010 and 0730 hours 4/8/2010?)

Any thoughts would be greatly appreciated.

Bob
 


Hi,

Where are the other 2 dates?

1. in 2 cells? If so what cells
2. in 2 variables? If so what variable, how declared? How assigned?
3. hard coded in an expression? If so what expression?

Please post any code that you are using that is relevant to this issue.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Note: the cell F2 contains a date only, not a time.
The value in excel is a number. Whether the date, or time, or date and time are displayed is a format issue, it does not change the number.



_________________
Bob Rashkin
 
dates are stored as a number with a integer part and a decimal part. The integer part represents the number of days since a base date (12/31/1899). The decimal part is the fraction of day representing the time. If you do not enter a time the decimal part defaults to 0 or 12:00 am.

so for your values:
stored value
4/8/2010: 40276
4/7/2010 11:30:00 PM: 40275.9791666667
4/8/2010 7:30:00 AM: 40276.3125

40267: 40267 days since 12/31/1899 and 0 percent of a day

40276.3125: 40267 days since 12/31/1899 and .3125 of a day or 0730

so any value without a time should default to 12:00 am.

however you need to be careful, because you can format a value with a date and time to show only the date portion.
 
Here is the code I am currently using (fdate = F2):

Code:
If fdate = (Format(Date, "mmddyyyy") And timenow < #7:30:00 AM#) Then

The only date in a cell is the date located in F2. The date comparison would be based on that date.

Is the current date & time between 2330 hours on F2 date-1 and 0730 hours on F2 date.

(ex: F2 = 4/8/2010, is the current date and time between 2330 hours 4/7/2010 and 0730 hours 4/8/2010?)

The dates referenced would then be calculated off that cell (F2 date - 1 = 4/7/2010 & F2 date = 4/8/2010). The times (2330 & 0730) would need to be added (these are fixed times) to these dates as well in order to do the comparison to the current date and time. (ex: 4/7/2010 2330 hrs & 4/8/2010 0730 hrs)

It will always be (F2 date - 1 at 2330 hours) and (F2 date at 0730 hours).
 
If Now >= fdate - #00:30:00# And Now <= fdate + #07:30:00# Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 


Code:
    If fDate >= Date + #11:30:00 PM# Then
        If fDate < Date + 1 + #7:30:00 AM# Then
           'Houston, we have a hit!

        End If
    End If

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
SkipVought and PHV,

Thank you both. Both ways actually work. Kudos to both of you and of course a star.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top