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!

 

[Fluff]

How about

Function Newdate(Rng As Range)
Newdate = Evaluate(Replace("EOMONTH(EDATE(DATE(YEAR(@),FLOOR(MONTH(@)-1,3)+1,1),3),-1)", "@", Rng.Address))
End Function

 

[stumac]

"The purpose of it is to get a date and round it to the next quarter end"

I use the following function:

Function LastDayInQuarter(Dte As Date) As Date
LastDayInQuarter = DateSerial(Year(Dte), Int((Month(Dte) - 1) / 3) * 3 + (3 + 1), 0)
End Function

 

[NewUser2]

Thanks Fluff,

But I am not knowledgeable enough to implement your suggestion. I'm assuming I replace you're @'s with the cell reference? Making a blank workbook and putting a date in cell b1 of sheet1.... where can i go from here to test this out?

How about

Function Newdate(Rng As Range)
Newdate = Evaluate(Replace("EOMONTH(EDATE(DATE(YEAR(@),FLOOR(MONTH(@)-1,3)+1,1),3),-1)", "@", Rng.Address))
End Function

 

[Fluff]

Type =Newdate(B1) into any cell, where B1 contains the date.
So if B1 has 1/11/2017, the function will return 31/12/2017

PS you don't need to make any changes to the code

 

[NewUser2]

Thanks, works great!
And yeah, sorry wasnt clear. I know how to do it in a worksheet. wanted to call it from a macro. But figured it out:

Sub test()
Dim Newdate As Range
Dim Appdate As Date


Dte = Worksheets("Sheet1").Cells(1, 4)
Appdate = LastDayInQuarter(Dte)
End Sub

Type =Newdate(B1) into any cell, where B1 contains the date.
So if B1 has 1/11/2017, the function will return 31/12/2017

PS you don't need to make any changes to the code

 

[Rick Rothstein]

Here is another function that you can use...

[table="width: 500"]
[tr]
	[td]Function LastDayInQuarter(Dte As Date) As Date
  LastDayInQuarter = DateSerial(Year(Dte), 3 * Format(Dte, "q") + 1, 0)
End Function[/td]
[/tr]
[/table]

 

[Fluff]

Glad you got it sorted & thanks for the feedback

 

[NewUser2]

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 go down to 12/31/2017.

Thanks again!

Type =Newdate(B1) into any cell, where B1 contains the date.
So if B1 has 1/11/2017, the function will return 31/12/2017

PS you don't need to make any changes to the code

 

[Rick Rothstein]

Here is another function that you can use...

[table="width: 500"]
[tr]
	[td]Function LastDayInQuarter(Dte As Date) As Date
  LastDayInQuarter = DateSerial(Year(Dte), 3 * Format(Dte, "q") + 1, 0)
End Function[/td]
[/tr]
[/table]

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"]
[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]
[/table]