date format (1 Viewer)

venu_resoju

Registered User.
Local time
Today, 19:49
Joined
Dec 29, 2010
Messages
130
Hai Friends...

I have text box on my report, in which I want to display as "01-Apr-2015" for first half of the financial year.for this I have given a code in the text box is

="021"&"-"& IIf((Format(Now(),"mmm")="Apr",Format(Now(),"mmm")="May",Format(Now(),"mmm")="Jun",Format(Now(),"mmm")="Jul",Format(Now(),"mmm")="Aug",Format(Now(),"mmm")="Sep"),"Oct","Apr")&"-"&"2015"
but it is not working
but
=IIf((Format(Now(),"mmm")="Apr",Format(Now(),"mmm")="May",Format(Now(),"mmm")="Jun",Format(Now(),"mmm")="Jul",Format(Now(),"mmm")="Aug",Format(Now(),"mmm")="Sep"),"Oct","Apr")
it results "Apr"

how can I farm this
please help me as I wanted result.
I am requiting you all
thanking you.
 

pr2-eugin

Super Moderator
Local time
Today, 14:19
Joined
Nov 30, 2011
Messages
8,494
What is that you are exactly trying to do? Could you explain that in simple words?
 

vbaInet

AWF VIP
Local time
Today, 14:19
Joined
Jan 22, 2010
Messages
26,374
Confused as well!

You want to return the 1st of April for the current year in the format "01-Apr-2015"?
Code:
"01-Apr-" & Year(Date())
 

venu_resoju

Registered User.
Local time
Today, 19:49
Joined
Dec 29, 2010
Messages
130
Thank you my dear friends,

I am trying to get.....

If current month is Apr to Sep ( Apr or May or Jun or Jul or Aug or Sep), I want to get the text box result is "01-Apr-current year".

If current month is Oct to Dec ( Oct or Nov or Dec), I want to get the text box result is "01-Oct-current year".

If current month is Jan to Mar ( Jan or Feb or Mar), I want to get the text box result is "01-Oct-(current year-1)".

I want to get these results for a text box on my ESIC form-37.

how can I get this, please help me to solve this.

Thanking you all
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:19
Joined
Aug 11, 2003
Messages
11,696
Lets first format your IIF
Code:
="021"&"-"& IIf((Format(Now(),"mmm")="Apr"
                ,Format(Now(),"mmm") ="May"
                ,Format(Now(),"mmm")="Jun"
                ,Format(Now(),"mmm ")="Jul"
                ,Format(Now(),"mmm")="Aug"
                ,Format(Now(),"m mm")="Sep"),"Oct","Apr")&"-"&"2015"
For this to "somewhat" work, you would want to use OR, not use the comma notation you have.
Code:
="021"&"-"& IIf((Format(Now(),"mmm")="Apr"
              or Format(Now(),"mmm") ="May"
              or Format(Now(),"mmm")="Jun"
              or Format(Now(),"mmm ")="Jul"
              or Format(Now(),"mmm")="Aug"
              or Format(Now(),"m mm")="Sep"),"Oct","Apr")&"-"&"2015"
I will let you fix your own typo's on top of that I would not use strings rather use i.e. month to get a number, which works so much more simple
Code:
IIF( Month(date()) >= 4 
 and Month(date()) <= 9, ....

The easiest way overall to do this... depends on how often will you use this.... or simular "awkward" date functions like fiscal quarter or fiscal month.

If you only have this year thing to do and if its used in a limited amount of places... lets say only in 1 or max say 3 places.... inline function will work just fine... otherwize you may want to put this into a function.
Inline:
Code:
IIF( Month(Dateadd("M", -3, Date())) <= 6, "01-Apr-"
                                         , "01-Oct-") & Year (Dateadd("M", -3, Date()))
Re-writing this to a proper function shouldnt be too hard, so I will leave that (for now) up to you.

Also you may want to tweak this so that you dont have a TEXT string, but rather have a "proper" date, working with text instead of dates can cause havoc down the line.

If you have multiple fiscal things to work with a date dimension table will work best. A table that contains all dates from say 01-01-2000 thru 31-12-2050 (or some date in the future) and the appropriate values you need for fiscal year logic as well as (for example) workday logic that you may need.
 

venu_resoju

Registered User.
Local time
Today, 19:49
Joined
Dec 29, 2010
Messages
130
Thank you my friend namliam...and all.....Thank you...

wonderful... it is working...

I can't express in my words..I am very happy..

I have been trying to get this for a very long time, but now I got it...
I am always thankful to you my friend...

Thanking you..
 

Users who are viewing this thread

Top Bottom