nexting functions in VBA

[NewUser2]

I wish it was as easy as pasting creative formulas from Excel to VBA, but alas it is not, which is why i am here...

I have this formula working in a cell, but would like it to work in VBA:
=EOMONTH(EDATE(DATE(YEAR(AA3),FLOOR(MONTH(AA3)-1,3)+1,1),3),-1)

The purpose of it is to get a date and round it to the next quarter end. For example, AA3 = Nov 1, 2017 and the formula results in Dec 31, 2017 (but in date format).

From reading some posts online about the year function it said to write it out something like this:
NewDate = EOMONTH(EDATE(DATE(YEAR(worksheets("MtgFile").cells(y,27)),FLOOR(MONTH($AA3)-1,3)+1,1),3),-1)

However, that's just for the year function. I dont know how all the rest of these work when nested.

The error I am getting is:
Compile error: Expected: )

And it's highlighting 'YEAR' in the formula above


Thanks in advance!

 

[Rick Rothstein]

I guess while I have you two Date guru's.... Any ideas on modifications to those 2 functions that will round the result? If I enter Jan 5, 2018, it rounds up to March 31, 2018. But it would be good if it did a logical round to the nearest quarter end. So that would

If I understand what you are looking for correctly, this would be the function (note the name change)...

[table="width: 500"]
[tr]
	[td]Function RoundedLastDayInQuarter(Dte As Date) As Date
  Dim MidQuarter As Date
  MidQuarter = Int(Application.Average(DateAdd("q", Format(Dte, "q"), "1/1/" & Year(Dte)), DateAdd("q", Format(Dte, "q") - 1, "1/1/" & Year(Dte))))
  RoundedLastDayInQuarter = DateSerial(Year(Dte), 3 * (Format(Dte, "q") + (Dte < MidQuarter)) + 1, 0)
End Function[/td]
[/tr]
[/table]

and this would be the macro...

[table="width: 500"]
[tr]
	[td]Sub LastDayRoundedToNearestQuarter()
  Dim Dte As Date, Appdate As Date, MidQuarter As Date
  Dte = Sheets("Sheet1").Range("D1")
  MidQuarter = Int(Application.Average(DateAdd("q", Format(Dte, "q"), "1/1/" & Year(Dte)), DateAdd("q", Format(Dte, "q") - 1, "1/1/" & Year(Dte))))
  Appdate = DateSerial(Year(Dte), 3 * (Format(Dte, "q") + (Dte < MidQuarter)) + 1, 0)
End Sub[/td]
[/tr]
[/table]

 

[NewUser2]

Oh fantastic, that works too! Thanks again.

Any idea on the quarter rounding problem I mentioned above?

Maybe i can also point you toward my other problem..... https://www.mrexcel.com/forum/excel-questions/1052282-vba-date-match-rounding.html

- The main issue in that other post is what you've started to solve here. (thanks again) However I'm also trying to see if there's a better / faster way to loop through all my data while looking up these dates (and offsetting from them to return another value).

Cheers

Edit Note: Just saw your need for this to be a macro... no need for the function as you can embed the code directly. Give this a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub test()
  Dim Dte As Date, Appdate As Date
  Dte = Sheets("Sheet1").Range("D1")
  Appdate = DateSerial(Year(Dte), 3 * Format(Dte, "q") + 1, 0)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Any idea on the quarter rounding problem I mentioned above?

Are you able to see Message #11 that I posted a little over 3 hours ago?

 

[NewUser2]

Aw yes, there it is. Thanks. Yeah #11 wasn't showing up when i wrote my last response. But that works perfectly for what I am trying to do. You are the best.

Are you able to see Message [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=11]#11 [/URL] that I posted a little over 3 hours ago?