DATE format

amsaini15

New Member
Joined
Aug 31, 2014
Messages
12

Hi All
Hoping one of the brilliant people on this forum can help me solve this dilemma .


Sheet: MapblasterData

PTWREF

<tbody>
</tbody>
REPORTEDDATE

<tbody>
</tbody>
CHG007775501-09-14 08:45
CHG007775530-08-14 12:29
CHG008542331-08-14 09:00
CHG008382431-08-14 12:29
CHG007775501-09-14 15:20
CHG007775501-09-14 10:11
CHG008222730-08-14 16:03

<tbody>
</tbody>

1. I have a combobox to load PTWREF values. On change event, I would like to see total number of reports against selected PTWREF with daily breakdown (I do not want to consider Time).


e.g. On Selecting "CHG0077755" in the combobox, Output should look like




DateNumber of Reports
30-08-141
01-09-143

<tbody>
</tbody>



Also I have a cmdbutton to create corresponding 2D chart with Date on H-Axis and Number of Reports on Y-axis (whole number (1,2,3, etc) only on Y-Axis).


Code:
Dim intMaxRows As Integer


Private Sub CmbPTWList_Change()
Dim RowC As Integer


'working query
strSQL = "SELECT [MapblasterData$].[REPORTEDDATE],Count([MapblasterData$].[REPORTEDDATE]) FROM [MapblasterData$] WHERE ([MapblasterData$].[PTWREF]) = '" & CmbPTWList.Text & "' GROUP BY [MAPBLASTERDATA$].[REPORTEDDATE],[MAPBLASTERDATA$].[PTWREF] ;"


closeRS
OpenDB


rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
            
If rs.RecordCount > 0 Then
            Sheets("PTWRaised").Visible = True
            Sheets("PTWRaised").Select
            'Range("dataSet").Select
            Range(Selection, Selection.End(xlDown)).ClearContents
            Range("B6:C50").Clear
            'Now putting the data on the sheet
            'ActiveCell.CopyFromRecordset rs
            Range("B6").CopyFromRecordset rs
Else
            MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
            closeRS
            Exit Sub
End If
            


intMaxRows = rs.RecordCount ' to use for specifying range for charts
            
Set cnn = Nothing
Set rs = Nothing  ' dispose the recordset


Sheets("PTWRaised").Columns("C").Cells.HorizontalAlignment = xlCenter


Dim StrSiteName As String
' vlookup for Site Name in another sheet 'SM9 Export' retrieved into a multipline textbox
Set ShtMapblasterData = ActiveWorkbook.Sheets("MapblasterData")
Set ShtSM9Export = ActiveWorkbook.Sheets("SM9 Export")
StrSiteName = Application.WorksheetFunction.VLookup(CmbPTWList.Text, ShtSM9Export.Range("A1:B10000"), 2, False)


TextBox1.MultiLine = True
TextBox1.AutoSize = False
TextBox1.Value = StrSiteName


CmdCreateChart_Click ' calling create chart event


End Sub
------------------------------------------------------------------------------------------------------------------------------

Private Sub CmdCreateChart_Click()
Range("B5:C" & (5 + intMaxRows) & " ").Select
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="PTWRaised"
End Sub


Problem with Above code:

1. It is taking into account time in REPORTEDDATE field as well and hence showing 3 different rows for 01-09-2014 as I need daily reports only. Currently date column in the output is showing date in dd/mm/yyyy format but formula bar shows dd/mm/yyyy hh:mm:ss.

2. some rows are changing date format to mm/dd/yyyy by itself. I have column formatting set to dd/mm/yyyy even.

Please help Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

amsaini15

New Member
Joined
Aug 31, 2014
Messages
12
Remove Duplicate from list and sum up

Hi All
After playing around with the code, I have used the DATEVALUE function to extract just the date from the date/time results. Now my outcome looks like:



Reported DateNumber of Reports
22-08-141
22-08-141
27-08-141
27-08-143
27-08-141
31-08-141
31-08-147

<tbody>
</tbody>


I need to code to remove duplicate dates and sum up corresponding 'Number of reports". Also would require another row to sum total reports

Output should look like


Reported DateNumber of Reports
22-08-142
27-08-145
31-08-148
Total Reports15

<tbody>
</tbody>


Thanks in advance for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top