Date function for Excel

[cairo207]

I require a function that takes a date dd/mm/yyyy and checks whether the dd/mm entered is >= 05/04

If Yes, the function returns the year of the date entered

If No, the function returns the year of the date entered minus 1

I've tried using a boolean to add the year without success.

 

[xlbo]

Logic and SELECT CASE should be all that is required here

Function WhatYear(DateVar as date)
Select case Month(DateVar)
  case 4
    if day(DateVar)>5 then
        WhatYear = Year(DateVar)
    else
        WhatYear = Year(DateVar)-1
    end if
  case >4
    WhatYear = Year(DateVar)
  case else
    WhatYear = Year(DateVar)-1
End Select
End function

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PHV]

And what about this formula ?
Year(theDate) + (Format(theDate, "mmdd") < 0405)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ETID]

...or this formula (assuming the dates are in col A)



=IF(AND(MONTH(A2)=5,DAY(A2)=4),YEAR(A2),"No Match")


....fill down

 

[ETID]

Oooops... if looking for April 5th then


=IF(AND(MONTH(A8)=4,DAY(A8)=5),YEAR(A8),"No Match")

 

[xlbo]

ETID - looking for 2 discreet answers
1 if < 05/04 apply date year
2 if >= 05/04 apply date year -1

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

Ahhhh....thanks for the slap in head

how's this?
=IF(AND(MONTH(A2)=4,DAY(A2)=5),YEAR(A2),YEAR(A2)-1)

 

[ETID]

...oh I see (not awake yet...nevermind)

 

[xlbo]

heh heh - tried to do it in a one liner myself to start with but I don't think you can as the number of days through the year will vary dependant on if there is a leap year or not - therefore, need to do 2 tests - one for if month = 4 and one for if month > 4 - just leaves the ELSe to handle the rest

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

I think I got it now....


=IF(A2>=DATEVALUE("4/5/"&YEAR(A2)),YEAR(A2),YEAR(A2)-1)

 

[xlbo]

Nice one ETID !!


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

..Thanks, All I needed was to get more than 2 brain cells to touch each other this A.M.

 

[cairo207]

Thanks for the help guys.