InputBox Date Value

[Vikram Dhemare]

Hi all,
I have created a inputbox where user has to put the date, the default value
is Cdate i.e today's date in DD/MM/YYYY format.
The variable report date is set according to the input box. Upto here, its
working fine. I am getting the desired report.
But if the user only enter the month & year in the input box, the system
itself define the variable to first date of the month, which i dont want. in
this case the variable should set only for mm/yyyy & not the dd/mm/yyyy
Can it be possible as I have created a report based on this. such as if user
wants the output for the particular date, user must enter the desired date
and if user want the output for the particular month then inputbox value must
return the datevalue for month & year.
Sub Tester()
Dim myDate As Date

myDate = Application.InputBox(Prompt:="Enter the Report Date", _
Title:="Date Parameter !", Default:=Format(CLng(Date),
"DD/MM/YYYY"))>>>>> Here (in the inputbox) if I put the value 06/2008 then
dateValue should return for the month of Jun 2008 but the value is returning
01/Jun/2008 And if I put the datevalue as 06/09/2008 then datevalue should
return 06/sept/2008.

MsgBox (myDate)


End Sub
I am using this for sumproduct function.
Application.Evaluate("=sumproduct(('Issue'!D265500= """ _
& myCell.Value & """ )*('Issue'!B2:B65500 =" & RptDt &
"),('Issue'!G2:G65500))")
Plz. help.

 

[Chris VN]

Hi Vikram

You can use:

DateTime.Month(myDate) to extract the month
DateTime.Year(myDate) to extract the year.

So, for example, you could test for the condition of the user entering
XX/XXXX instead of XX/XX/XXXX, and if they type XX/XXXX (month/year), then:
Msgbox DateTime.Month(myDate) & '/' & DateTime.Year(myDate)

Is this helpful information for you?

Let me know if this solves your problem.

Best of Luck!

 

[Vikram Dhemare]

Hello Mr. Chris,

Thanks for your early reply. Since I am using this to evaluate sumproduct
function it is not useful or I might have no idea, how to use it ?
Any help in this regard will be very useful to serve my mgmt with dynamic
results.

 

[Rick Rothstein]

You could check how many forward slashes are in myDate and if only one,
patch a day value into the string so date can be parsed/formatted correctly.
I'm thinking that immediately after the user gives you his/her date via the
InputBox, you could do this...

If UBound(Split(myDate, "/")) = 1 Then myDate = "01/" & mdDate

and then continue with the rest of your code. I'm assuming in some part of
the rest of your code, you check to make sure you have a legitimate date.

 

[Vikram Dhemare]

Not working.
--
Thanks,
Vikram P. Dhemare

You could check how many forward slashes are in myDate and if only one,
patch a day value into the string so date can be parsed/formatted correctly.
I'm thinking that immediately after the user gives you his/her date via the
InputBox, you could do this...

If UBound(Split(myDate, "/")) = 1 Then myDate = "01/" & mdDate

and then continue with the rest of your code. I'm assuming in some part of
the rest of your code, you check to make sure you have a legitimate date.

 

[Rick Rothstein]

Do you think you could provide more details other than saying "not working"?
In what way is it not working? What is it actually doing that you think is
not right? (Hint... based on how I interpreted your question, the code I
posted worked fine during my testing of it before I posted it.)

--
Rick (MVP - Excel)

Not working.

 

[Vikram Dhemare]

I thing, I have not expressed my problem correctly. Basically I have two
sheets. One sheet is having unique Item list of a firm and other one is the
detailed transaction database with date/time stamping colulmn. Now I want to
users have an option whether to have the cumulative data or datewise data in
front of unique item list by using sumproduct funtion.
Can u help me to come out of this issue?
Formula I tried :
ans1 = Application.Evaluate("=sumproduct(('OSP'!B4:B65500= """ _
& myCell.Value & """ )*('OSP'!E4:E65500 =" & myDate &
"),('OSP'!D465500))")

The data looks like:
Database
M1171 316 9/8/08 12:00 AM
M1171 -216 9/7/08 11:58 PM
M1173 500 9/8/08 11:58 PM
M1173 -108 9/3/08 11:58 PM
M1179 10 9/8/08 11:57 PM
M1179 -270 9/1/08 11:57 PM
M1495 36 9/8/08 11:53 PM
M1494 36 9/8/08 11:53 PM
M0507 31 9/8/08 11:53 PM
M0506 31 9/8/08 11:53 PM

Result for cumulative figure

M1171 100
M1173 392
M1179 -260
M1495 36
M1494 36
M0507 31
M0506 31

Result for datewise figure (for the date of 07-09-2008)
M1171 -216
M1173 0
M1179 0
M1495 0
M1494 0
M0507 0
M0506 0