How to use formulae to change date and time formats in WebIntelligence
In Webi there are multiple way in which you can change the format of the displayed date and time field according to requirement .
There are some date format which are available in Webi . Please note that it is case sensitive so it will make a difference if you write Ddd and DDD.
Please see the below usage of Format for more understanding :
|Input date||String date format||formula||output|
|25/09/2013||d||FormatDate(datefield(); “d” )||25|
|25/09/2014||dd||FormatDate(datefield(); “dd” )||25|
|25/09/2015||Ddd||FormatDate(datefield(); “Ddd” )||Wed|
|25/09/2016||DDD||FormatDate(datefield(); “DDD” )||WED|
|25/09/2017||Dddd||FormatDate(datefield(); “Dddd” )||Wednesday|
|25/09/2018||DDDD||FormatDate(datefield(); “DDDD” )||WEDNESDAY|
|25/09/2019||Dddd dd||FormatDate(datefield(); “Dddd dd” )||Wednesday 25|
|25/09/2020||M||FormatDate(datefield(); “M” )||9|
|25/09/2021||MM||FormatDate(datefield(); “MM” )||09|
|25/09/2022||Mmm||FormatDate(datefield(); “Mmm” )||Sep|
|25/09/2023||Mmmm||FormatDate(datefield(); “Mmmm” )||September|
|25/09/2024||yy||FormatDate(datefield(); “yy” )||13|
|25/09/2025||yyyy||FormatDate(datefield(); “yyyy” )||2013|
|25/09/2026||Dddd dd Mmmm yyyy||FormatDate(datefield(); “Dddd dd Mmmm yyyy” )||Wednesday 25 september 2013|
|25/09/2027||h:mm:ss||FormatDate(datefield(); “h:mm:ss” )||11:09:08|
|25/09/2028||hh:mm||FormatDate(datefield(); “hh:mm” )||11:09|
|25/09/2029||hh:mm:ss||FormatDate(datefield(); “hh:mm:ss” )||11:09:08|
|25/09/2030||dd/MM/yyyy hh:mm:ss||FormatDate(datefield(); “dd/MM/yyyy hh:mm:ss” )||25/09/2013 11:09:08|
|25/09/2030||dd/MM/yyyy hh:mm:ss AM (or PM)||FormatDate(datefield(); “dd/MM/yyyy hh:mm:ss a” )||25/09/2013 11:09:08 PM|
|25/09/2030||dd/MM/yyyy HH:mm:ss||FormatDate(datefield(); “dd/MM/yyyy HH:mm:ss”)||25/09/2013 23:09:08|
These are some standard formula which we can use to get the desire format. Apart form this we may have date and time functions in formula editor which we can use according to usage to achieve desire format.
like in formula editor you will find in “Year” which will return year in a Date , “MonthNumberOfYear” it will return the number in a date , “DayNumberOfYear” returns the day number in a year etc.
Problem statement :
I have requirement to provide the report in up-loadable date and time format . That means, date in yyyymmdd format and time in hhmmss format.
To achieve the requirement of date format we can use the following formula in Webi :
FormatNumber(Year(Date field()); “0000”)+FormatNumber(MonthNumberOfYear([Planned Call Date]);”00″ )+FormatNumber(DayNumberOfYear([Planned Call Date]); “00”) .
So, for the input date for eg :”25/09/2013″ the formula gives the output as “20130925”.
Now to for the time format i went on the logic to convert it to date using function “ToDate” and then use “FormatDate” function.
Below is the output which i got : 1st field is the time field, 2nd is after applying formula to convert to date field and the 3rd one is after formatting of date field :
So formula will throw error for the time 15:25:23 , because it is unable to convert it to date format. For the other time its working fine and formatting is done accordingly.
Now, if you see the time filed format (HH:MM:SS) and require format of time (HHMMSS) , we can achieve this by removing the colon(:) from the time field.
There is function in BO formula editor called “Replace” which replaces the part of string with another string.
so formula = Replace([Time field] ; “:”;”” ) will replace the “:” with blank .
There are so many functions in the formula editor to make the job easy, we have to be aware of working and functionality of formula.
Helpful Info , thanks For Sharing.
Thanks Seshu for encouragement
Thanks for sharing the useful info.
pls could you change the title to something like "How to use formulae to change date and time formats in Webi" .
as it currently stands, is not so meaningful.
Hi Henry ,
i will implement the change soon , thanks for your suggestion
Superb Neha.. Keep it up!
its Good `````
I have started working on WebI lately so these tips and tricks will definitely be helpful.
Thanks for sharing this info.
Thanks a lot Anshu
Very informative, it can help beginners to start with webi reports ..
hey its good..small but usefull tips..
thanks for sharing...!!
Very useful information keep sharing.......
needful information, thanks for sharing:)
Good information Neha.. Keep posting!!
Thanks Laxmi for the encouragement
Neha Kumari,You are an angel .
What I need
Smart solution steps for smart developers 🙂
Thanks a lot Muthukumar for encouragement
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
Hey William , i have seen your document and it is really very helpful. Keep sharing and thank you for including this
Useful info... thanks 4 sharing..
Great piece of Information... Thanks for your efforts and for sharing Neha..Thanks Henry for your corrections as well.
In addition to your list of date formats above, I've found AM/PM and Timezone to also be handy:
FormatDate(dateField(); "hh:mm:ss a z") 11:09:25 AM GMT+06:00
Hi Ryan ,
Thanks for this information
Another parameter worth noting is the "INPUT_DATE_TIME" / "INPUT_DATE" parameter, which formats the date per locale.
thanks Neil nice piece of info
Thanks Neha. I get ample of requests to address these sorts of date formats.. This document has been a great help..
Thanks a lot Vinay
Thanks for sharing. Good work.....
Related with my post Date Variables in Web Intelligence
Thank you, these are really cool formatting tips for beginners..
There are many other Constants for formatting which changes as per Preferred Viewing Locale. Such as:
The following table explains it:
Thanks for adding to the above information!
Thanks for sharing.
Nice one doc.
Thnaks for sharing
Very helpful document.
I think there is an error in the formula. You use DayNumberOfYear where I think it should be DayNumberOfMonth.
Thanks for sharing . I really appreciate time and effort!
just I tried to use one of the date format which is mentioned here
FormatDate(datefield(); "dd/mm/yyyy" - it displayed as 28/00/2015 - Month is displayed as 00 ,after used MM in caps then it displayed a correct date,
FormatDate(datefield(); "dd/MM/yyyy" - it displayed as 28/05/2015 - correct result
why we should enter the MM is caps, any specific reason,
I am tried this is BO 4.0 SP10, date is retrived from BW Bex option.
mm: Minutes and MM: Month.
I wouldn't think so as some weeks cover 2 months. You are better off using a specific date, maybe use the Friday week ending date to group them.
thank you for sharing
Thanks, very useful
I need Help: Case: i need to come up with a date format per digit each day, per digit month and per digit year. Each has to fit in a box independently from each other. I like to accomplish this in the Formula Editor of the Properties Field, using the available variable or else