Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
Have been trying to write my first custom function, to determine if a year is
a leap year, using the following formula provided by Microsoft: =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a Leap Year") In my worksheet, for example, I plan to put the following formula in cell B1: =if(LeapYear(A1) = "leap",c209/t13,b23*U14). I cannot get the custom function (below) to work properly; it either comes back with an error, returns 0, or the wrong answer. So far, I've got: Public Function LeapYear(MyRange As Range) As Integer Dim ActiveCell As Range On Error GoTo LeapYearErr Application.Volatile LeapYear = "=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),""leap"",""not"")" LeapYearErr: Exit Function End Function Can someone advise what I'm doing wrong here? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
Here is a function that should work for you. It returns a True or False
Public Function LeapYear(Cell As Range) As Boolean If Cell.Value Mod 400 = 0 Or (Cell.Value Mod 4 = 0 And Cell.Value Mod 100 < 0) Then LeapYear = True Else LeapYear = False End If End Function So in B1 you could use =if(LeapYear(A1) ,c209/t13,b23*U14) -- HTH... Jim Thomlinson "Paige" wrote: Have been trying to write my first custom function, to determine if a year is a leap year, using the following formula provided by Microsoft: =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a Leap Year") In my worksheet, for example, I plan to put the following formula in cell B1: =if(LeapYear(A1) = "leap",c209/t13,b23*U14). I cannot get the custom function (below) to work properly; it either comes back with an error, returns 0, or the wrong answer. So far, I've got: Public Function LeapYear(MyRange As Range) As Integer Dim ActiveCell As Range On Error GoTo LeapYearErr Application.Volatile LeapYear = "=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),""leap"",""not"")" LeapYearErr: Exit Function End Function Can someone advise what I'm doing wrong here? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
Public Function LeapYear(yr as Long) as Boolean
LeapYear = (month(date(yr,2,29)) = 2) End Sub -- Regards, Tom Ogilvy "Paige" wrote: Have been trying to write my first custom function, to determine if a year is a leap year, using the following formula provided by Microsoft: =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a Leap Year") In my worksheet, for example, I plan to put the following formula in cell B1: =if(LeapYear(A1) = "leap",c209/t13,b23*U14). I cannot get the custom function (below) to work properly; it either comes back with an error, returns 0, or the wrong answer. So far, I've got: Public Function LeapYear(MyRange As Range) As Integer Dim ActiveCell As Range On Error GoTo LeapYearErr Application.Volatile LeapYear = "=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),""leap"",""not"")" LeapYearErr: Exit Function End Function Can someone advise what I'm doing wrong here? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
Tom Ogilvy wrote:
Public Function LeapYear(yr as Long) as Boolean LeapYear = (month(date(yr,2,29)) = 2) End Sub I was going to offer something similar (subtract 1 from 3/1 of the year to see if the day is 28 or 29). However, basing the leap year decision on whether the year has a 2/29 date appears to incorrectly identify 1900 as a leap year? EXCEL's date function claims there is a date of 2/29/1900. Plus, it only works back to 1900. Which may or may not be a problem, depending on how it is being used. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
Thanks, guys; will give these a try. Appreciate your help!
"Randy Harmelink" wrote: Tom Ogilvy wrote: Public Function LeapYear(yr as Long) as Boolean LeapYear = (month(date(yr,2,29)) = 2) End Sub I was going to offer something similar (subtract 1 from 3/1 of the year to see if the day is 28 or 29). However, basing the leap year decision on whether the year has a 2/29 date appears to incorrectly identify 1900 as a leap year? EXCEL's date function claims there is a date of 2/29/1900. Plus, it only works back to 1900. Which may or may not be a problem, depending on how it is being used. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
What you say about Excel is true. But I am not using Excel. I am using
VBA, so it does not have these limitations. -- Regards, Tom Ogilvy "Randy Harmelink" wrote: Tom Ogilvy wrote: Public Function LeapYear(yr as Long) as Boolean LeapYear = (month(date(yr,2,29)) = 2) End Sub I was going to offer something similar (subtract 1 from 3/1 of the year to see if the day is 28 or 29). However, basing the leap year decision on whether the year has a 2/29 date appears to incorrectly identify 1900 as a leap year? EXCEL's date function claims there is a date of 2/29/1900. Plus, it only works back to 1900. Which may or may not be a problem, depending on how it is being used. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
The 1900 error was deliberately included for compatibility with Lotus's
previous error. Excel's worksheet will only work back to 1900, but VBA Dates are valid back the year 100. However, given the number of changes to the calendars since then, I would not put much faith in reality v. calculation before the 1800s. NickHK "Randy Harmelink" wrote in message oups.com... Tom Ogilvy wrote: Public Function LeapYear(yr as Long) as Boolean LeapYear = (month(date(yr,2,29)) = 2) End Sub I was going to offer something similar (subtract 1 from 3/1 of the year to see if the day is 28 or 29). However, basing the leap year decision on whether the year has a 2/29 date appears to incorrectly identify 1900 as a leap year? EXCEL's date function claims there is a date of 2/29/1900. Plus, it only works back to 1900. Which may or may not be a problem, depending on how it is being used. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Custom Function - A Little Help Please!
<not put much faith in reality v. calculation before the 1800s.
The meaning of the date also depends on its location. In Russia, for example, the Gregorian calendar was adopted as late as 1917, and there are many other examples of other calendars being used in rather recent centuries. -- Kind regards, Niek Otten Microsoft MVP - Excel "NickHK" wrote in message ... | The 1900 error was deliberately included for compatibility with Lotus's | previous error. | Excel's worksheet will only work back to 1900, but VBA Dates are valid back | the year 100. | However, given the number of changes to the calendars since then, I would | not put much faith in reality v. calculation before the 1800s. | | NickHK | | "Randy Harmelink" wrote in message | oups.com... | Tom Ogilvy wrote: | Public Function LeapYear(yr as Long) as Boolean | LeapYear = (month(date(yr,2,29)) = 2) | End Sub | | I was going to offer something similar (subtract 1 from 3/1 of the year | to see if the day is 28 or 29). However, basing the leap year decision | on whether the year has a 2/29 date appears to incorrectly identify | 1900 as a leap year? EXCEL's date function claims there is a date of | 2/29/1900. | | Plus, it only works back to 1900. Which may or may not be a problem, | depending on how it is being used. | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating custom charts | Charts and Charting in Excel | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Creating an custom input box | Excel Worksheet Functions | |||
Creating a custom toolbar in VBA | Excel Programming | |||
Creating Custom Function: TRIMSTDEV | Excel Programming |