Solved

# Variable with date value showing up as Time only

Posted on 2012-09-04
Medium Priority
708 Views
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)
``````
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()
``````
and inputing the dayQuarter into the code
`````` Range("D17") = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
``````
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)
``````
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
``````
0
Question by:Daniel_P67

LVL 93

Accepted Solution

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)
``````
0

LVL 1

Author Closing Comment

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

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