?
Solved

Variable with date value showing up as Time only

Posted on 2012-09-04
2
Medium Priority
?
708 Views
Last Modified: 2012-09-04
Trying to find the value of the first day of the first month of the current quarter. But our first day is the first Monday of the first month. This code
 Range("D17") = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)

Open in new window

gives me the correct first day of the month. I have created a function see below code that gives me the first day(but not our first day) of the first month of the current quarter. The issue is the first day is the calendar first day and not our first day.

So i am taking the varable dayQuarter from
Dim dayQuarter As Date
dayQuater = dhFirstDayInQuarter()

Open in new window

and inputing the dayQuarter into the code  
 Range("D17") = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)

Open in new window

by replacing "Date" with the "dayQuarter" variable.

The issue i am having is
 Range("D20") = DateSerial(Year(dayQuarter), Int((Month(dayQuarter) - 1) / 3) * 3 + 1, 1)

Open in new window

fails because dayQuarter is showing up as a time value instead of a date value. When i assign dayQuarter it's value in debugging mode says its a date but the next time i call the value it changes to a time.

How can i keep dayQuarter as a date?


Dim TDateRange As Long
Dim TDateRange2 As Long
Dim i As Integer

Dim todaysDate As String
Dim weekDate As String
Dim monthDate As String
Dim quarterDate As String


     FirstDayInWeek = Date - _
 Weekday(Date, vbUseSystemDayOfWeek) + 2
 
 
Dim dayQuarter As Date
dayQuater = dhFirstDayInQuarter()
'Format(dayQuarter) As Date
'Range("D19").Value = Int(dayQuarter)
' dayQuarter = Format(dayQuarter, "mm/dd/yyyy")



 
 Range("D14").FormulaR1C1 = "=today()"
 Range("D15") = FirstDayInWeek
 Range("D16") = DateSerial(Year(Date), Month(Date), ((7 - Weekday(DateSerial(Year(Date), Month(Date), 7))) + 2) Mod 7)
 Range("D17") = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
 Range("D20") = DateSerial(Year(dayQuarter), Int((Month(dayQuarter) - 1) / 3) * 3 + 1, 1)
 todaysDate = Range("D14").Value
 weekDate = Range("D15").Value
 monthDate = Range("D16").Value
 quarterDate = Range("D17").Value
 
'more code

end sub


Function dhFirstDayInQuarter( _
 Optional dtmDate As Date = 0) As Date
    ' Returns the first day in the quarter specified
    ' by the date in dtmDate.
    Const dhcMonthsInQuarter As Integer = 3
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhFirstDayInQuarter = DateSerial( _
     Year(dtmDate), _
     Int((Month(dtmDate) - 1) / dhcMonthsInQuarter) * _
     dhcMonthsInQuarter + 1, _
     1)
End Function

Open in new window

0
Comment
Question by:Daniel_P67
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38365841
A Date data type always encodes both a time and a date.  Always.

Now, if you need to have the first day of the current quarter, with the stipulation that the quarter always begins on a Monday, that is quite easy to do:

FirstDayOfQuarter = DateAdd("q", DateDiff("q", 2, Now), 2) + 7 - _
    Weekday(DateAdd("q", DateDiff("q", 2, Now), 2), vbTuesday)

Open in new window

0
 
LVL 1

Author Closing Comment

by:Daniel_P67
ID: 38366157
The revised code work perfect. Still unclear why i was getting the error.
Many thanks!

Do you know why the time was showing up instead of the date?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question