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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,292
Messages
5,836,448
Members
430,431
Latest member
Tomexcel2022

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