Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with formula to check month/day range 4

Status
Not open for further replies.

huytonscouser

Programmer
Oct 14, 2011
88
US
if there an easy way to accomplish checking date range other than
using month(currentdate)and day(currentdate) ?

Using current date i want to check just month and day

i.e.

if (currentdate) is equal or between this range
//16-Nov 15-Dec Then "January"

else if (currentdate) is equal or between this range
//16-Dec 31-Dec Then "February A"

else if (currentdate) is equal or between this range
//1-Jan 15-Jan Then "February B"

else "All else
 
DatePart can isolate a month, and DateAdd can add a given number of time-units, anything from seconds to months. But your examples use variable time-spans, so I don't see any short way. Code something like
Code:
if DatePart ("m", Currentdate) = 11
    then if DatePart ("d", Currentdate) < 16
            then "December"
            else "January"
else if DatePart ("m", Currentdate) = 12
        then if DatePart ("d", Currentdate) < 16
                then "January"
                else "February A"
And so on.

If might be easier to put the month and day as separate formula fields and then invoke them by name.

[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]
 
Huytonscouser,

Is it just the 4 possibilities? or is it A & B based on the "Month-Half" and "x" months ahead of the dates analyzed? If just the four options, an IF using either DatePart() as Madawc suggests, or the Year(), Month() and Day() functions identified in your original post.

If it is something more dynamic, please advise - a formula could likely be written that auto-generates the text based on CurrentDate (or a date field were that the case).

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
Thanks Mike, heres the exact date ranges i'm trying to handle

//16-Nov 15-Dec = January
//16-Dec 31-Dec = February A
//1-Jan 15-Jan = February B
//16-Jan 15-Feb = March
//16-Feb 15-Mar = April
//16-Mar 15-Apr = May
//16-Apr 15-May = June
//16-May 15-Jun = July
//16-Jun 15-Jul = August
//16-Jul 15-Aug = September
//16-Aug 15-Sep = October
//16-Sep 15-Oct = November
//16-Oct 15-Nov = December
 
I would do it this way:

Code:
Select  (Month(CurrentDate) * 100) + Day(CurrentDate)
Case is <=  115 : 'February B'
Case is <=  215 : 'March'  
Case is <=  315 : 'April'
Case is <=  415 : 'May'
Case is <=  515 : 'June'
Case is <=  615 : 'July'
Case is <=  715 : 'August'
Case is <=  815 : 'September'
Case is <=  915 : 'October'
Case is <= 1015 : 'November'
Case is <= 1115 : 'December'
Case is <= 1215 : 'January'
Case is <= 1231 : 'February A'

Pete
 
Pete:

That is excellent! "Note to future self" should I ever be looking to do anything similar.

Have a star!
tipmasterstar.png


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
huytonscouser: Happy to help - glad it was useful.

Mike: Thanks for the star. Always nice to get them, particularly from fellow "helpers".

Pete,
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top