Convert column A dates to end of month for entire column range

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
Hi all!

I hope everyone is keeping well.

I am trying to convert all my dates in Column A to the End of Month dates from cell A2 to the end of the column of the used range.
I would like to do this for all sheets in the workbook which have the same structure with the DateHeading in column A.

For example, my data in Column a would be:

Date Format: YYYY-MM-DD

DateHeading

2020-04-01 > to become > 2020-04-30
2020-03-22 > to become > 2020-03-31
2020-02-06 > to become > 2020-02-28
2020-01-18 > to become > 2020-01-31

My current code is as follows:

VBA Code:
Sub ConvertTheDates1()
      Dim x As Integer
      numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      Range("A2").Select
      For x = 1 To numrows
      numrows = Excel.Application.WorksheetFunction.EOMONTH(Range("A2").Value2, 0)
         ActiveCell.Offset(1, 0).Select
      Next
   End Sub

If anyone is able to help me with this, you would be so much help!
Please let me know. I have been trying to solve this since Saturday! :censored:

Thank you all and regards,
Manerlao
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
Hi all, just an update. This VBA works, but it is really inefficient. I would need this to go down to a few thousand rows.

VBA Code:
Range("A2") = Excel.Application.WorksheetFunction.EOMONTH(Range("A2").Value2, 0)
Range("A3") = Excel.Application.WorksheetFunction.EOMONTH(Range("A3").Value2, 0)
Range("A4") = Excel.Application.WorksheetFunction.EOMONTH(Range("A4").Value2, 0)
Range("A5") = Excel.Application.WorksheetFunction.EOMONTH(Range("A5").Value2, 0)
Range("A6") = Excel.Application.WorksheetFunction.EOMONTH(Range("A6").Value2, 0)
Range("A7") = Excel.Application.WorksheetFunction.EOMONTH(Range("A7").Value2, 0)
Range("A8") = Excel.Application.WorksheetFunction.EOMONTH(Range("A8").Value2, 0)
Range("A9") = Excel.Application.WorksheetFunction.EOMONTH(Range("A9").Value2, 0)
Range("A10") = Excel.Application.WorksheetFunction.EOMONTH(Range("A10").Value2, 0)
Range("A11") = Excel.Application.WorksheetFunction.EOMONTH(Range("A11").Value2, 0)
Range("A12") = Excel.Application.WorksheetFunction.EOMONTH(Range("A12").Value2, 0)
Range("A13") = Excel.Application.WorksheetFunction.EOMONTH(Range("A13").Value2, 0)
Range("A14") = Excel.Application.WorksheetFunction.EOMONTH(Range("A14").Value2, 0)
Range("A15") = Excel.Application.WorksheetFunction.EOMONTH(Range("A15").Value2, 0)
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Your code
VBA Code:
Sub ConvertTheDates1()
      Dim x As Integer
      Dim numrows, i
      numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      For x = 2 To numrows + 1
      Cells(x, 2) = Format(WorksheetFunction.EoMonth(Range("A" & x), 0), "dd/mm/yyyy")
      Next
   End Sub
 

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
Dear Mohadin,

You are a legendary person! Thank you very much for this! It took be days to figure this out, and you solved it for me :) Thank you.

I just adjusted the code so that it replaces the dates in Column A to:

VBA Code:
Sub ConvertTheDates1()
      Dim x As Integer
      Dim numrows, i
      numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
      For x = 2 To numrows + 1
      Cells(x, 1) = Format(WorksheetFunction.EOMONTH(Range("A" & x), 0), "YYYY-MM-DD")
      Next
   End Sub

Thank you! :)
Manerlao
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
And thank you for the feedback
Be happy & safe
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
No need to loop.
VBA Code:
Dim rng As Range
Dim varEndDates As Variant

    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    
    varEndDates = Evaluate("DATE(YEAR(" & rng.Address & "),MONTH(" & rng.Address & ")+1,0)")

    rng.Value = varEndDates
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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