Date Formulas

[Carly]

I have a textbox with the following formula:

[txt1stdate] = DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1)

which comes out as the 1st day of the previous month (i.e. 01/07/2003).

What I would like to do is create another textbox which works out a date from the above textbox, The date I would like to work out is:

IF the MONTH of [txt1stdate] is 3(march), 4(april), 5(may), 6(june),7(july) or 8(august) then put 01/03/2003(currentyr)

BUT IF

the MONTH of [1sttxtdate] is 9(september), 10(october), 11(november), 12(december),1(january) or 2(february) then put 01/09/2002(prioryr)

Can this be done?
Regards
Carly

 

[namliam]

?iif(month(txt1stdate) >= 3 and month(txt1stdate) <=9,"This Year","LastYear")

This puts the textstring, use the dateadd or something simular to do the date thing...

Regards

The Mailman

 

[Mile-O]

From what you've said, "if the month of the first date is April (regardless of whether the year is 1974, 2003, or 2074) then make the second textbox equal 01/03/2003"


Is that what you want? Or have you just not made yourself clear enough?

 

[Carly]

NO what I want is if the [txt1stdate] is march, april, may, june, july or august then put in 01/03/(current year) otherwise put in 01/09/(prior year)

The reason being is that our trading periods run from Mar to Aug and Sep to Feb therefore the beginning of the trading periods are always Mar or Sep

 

[namliam]

iif(month(txt1stdate) >= 3 and month(txt1stdate) <=9,cdate("03/01/" & year(txt1stdate)) ,cdate("09/01/" & year(txt1stdate)-1))

 

[Carly]

I have used the following formula:

=IIf(Month([txt1stdate])>=3 And Month([txt1stdate])<=8,DateSerial(Year([txt1stdate]),3,1), IIf(Month([txt1stdate])>=9 And Month([txt1stdate])<=12,DateSerial(Year([txt1stdate]),9,1),IIf(Month([txt1stdate]) =1 or Month([txt1stdate])=2,DateSerial(Year([txt1stdate]-1),9,1),”Unknown”)))

This works apart from when I change [txt1stdate] to 01/02/2003, the date should be 01/09/2002 but it goes to 01/09/2003. I thought I was capturing February in the highlighted part

 

[namliam]

Yes you are and that is also the problem !

=IIf(Month([txt1stdate])>=3 And Month([txt1stdate])<=8,
DateSerial(Year([txt1stdate]),3,1),
IIf(Month([txt1stdate])>=9 And Month([txt1stdate])< =12,
DateSerial(Year([txt1stdate]),9,1),
IIf(Month([txt1stdate]) =1 or Month([txt1stdate])=2,
DateSerial(Year([txt1stdate]-1),9,1),
”Unknown”)))

Move the -1 outside the brackets of Year():

Year([txt1stdate])-1

Regards

 

[Carly]

Thank you!

 

[namliam]

however you function is not complying with your staments above....

9,10,11,12 should, accoording to you, also be year()-1

1 other thing, why add Unknow?

REgards

 

[Carly]

The unknown was just there to see whether February didn;t come into any of the other categories as I couldn't underststand why it wasn't changing.

9,10,11,12 do not need to be year()-1 because if the month is october then the start of the period is September in the same year. I didn't mention this is the first question as I knew if I could get the two senarios then I would be able to add the 3rd one in myself.

 

[raskew]

Try this little function:

Function TradingPeriod(pDate As Date) As Date
'*******************************************
'Name:      TradingPeriod (Function)
'Purpose:   Returns start date of trading
'           period relating to pDate
'Note:      Examples use U.S. short-date format
'Inputs:    (1) ? TradingPeriod(#2/23/03#)
'           (2) ? TradingPeriod(#3/23/03#)
'           (3) ? TradingPeriod(#8/23/03#)
'           (4) ? TradingPeriod(#12/23/03#)
            
'Output:    (1) 9/1/02
'           (2) 3/1/03
'           (3) 3/1/03
'           (4) 9/1/03
'*******************************************

Dim tpmonth As Integer, tpyear As Integer

tpmonth = IIf(InStr("3 4 5 6 7 8", Month(pDate)) > 0, 3, 9)
tpyear = IIf(Month(pDate) < tpmonth, -1, 0)
TradingPeriod = DateSerial(Year(DateAdd("yyyy", tpyear, pDate)), tpmonth, 1)

End Function