Extracting First and Last Date from a data extract, and filling cells with all dates between them

Jaevwyn

New Member
Joined
Oct 30, 2017
Messages
12
Hi Guys,

I am trying to make a template report which can take raw data from a certain program and display it in a much easier to read format. For the most part I have been successful, however I am trying to remove as many manual components from this as possible oh which two remain.
My focus here is that when bringing in the raw data into excel, Column H of this data will have various time stamps formatted "dd/mm/yyyy hh:mm". Is there a way to have excel read these dates, pick out the earliest and latest of them, and populate all dates including and inbetween these dates on a different sheet formatted as dd/mm/yyyy in, for arguments sake, sheet 2 column A.

As as example, my raw data could contain in column H:

11/12/2017 10:17
13/12/2017 17:19
15/12/2017 07:10

And I would like to see on sheet 2 column A

11/12/2017
12/12/2017
13/12/2017
14/12/2017
15/12/2017


It may be a bit of a stretch both for Excel and certainly myself, I am not an experienced user and have never needed to use VBA thus far.

Many Thanks! :)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Jaevwyn

New Member
Joined
Oct 30, 2017
Messages
12
As an after thought, I would like to note the dates may not always be in order!
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Code:
Public Sub wbd2017121303()

Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim firstDate As Date
Dim lastDate As Date
Dim thisRow As Long

Set sourceSheet = Sheets("Sheet1") ' Sheet with dates in column H
Set targetSheet = Sheets("Sheet2") ' Sheet to put the new dates

' Find the last row on the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "H").End(xlUp).Row

' Get the first and last dates from column H
firstDate = Int(Application.WorksheetFunction.Min(sourceSheet.Range("H1:H" & lastRow)))
lastDate = Int(Application.WorksheetFunction.Max(sourceSheet.Range("H1:H" & lastRow)))

' Populate the dates onto the other sheet
thisRow = 1
Do While firstDate <= lastDate
    targetSheet.Cells(thisRow, "A").Value = firstDate
    firstDate = firstDate + 1
    thisRow = thisRow + 1
Loop

End Sub

WBD
 

Jaevwyn

New Member
Joined
Oct 30, 2017
Messages
12
Wow, thanks for the fast reply!
Get ready for the stupid question though...

Where do i input this? I have never used VBA :confused:
 

Jaevwyn

New Member
Joined
Oct 30, 2017
Messages
12
Nevermind I found it, but its disabled on our laptops here by default, will get that resolved and inputted!
Thanks :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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