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.
Hi,
Helpful Info , thanks For Sharing.
Regards,
Seshu.P
Thanks Seshu for encouragement
Thanks for sharing the useful info.
thanks Raman
hi,
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.
regards.
H
Hi Henry ,
i will implement the change soon , thanks for your suggestion
Regrads,
Neha.
Superb Neha.. Keep it up!
Regards,
Shyam Alok
its Good `````
-Bhaskar
Thanks Bhaskar
I have started working on WebI lately so these tips and tricks will definitely be helpful.
Thanks for sharing this info.
Regards,
AL
Thanks a lot Anshu
really helpfull.....
Thanks Sampath
Very informative, it can help beginners to start with webi reports ..
Regards
Shabnam
Thanks Shabnam
hey its good..small but usefull tips..
thanks for sharing...!!
Thanks Prerna
simply superb
Thanks Krishna
Very useful information keep sharing.......
Regards,
SM
Thanks Santendra
needful information, thanks for sharing:)
Thanks Sudarshan
Good information Neha.. Keep posting!!
Thanks Laxmi for the encouragement
Informative!!
Thanks Thejas
Good document.....Helpful
Thanks Chandra
Neha Kumari,You are an angel .
What I need
Wang
Smart solution steps for smart developers 🙂
Thanks a lot Muthukumar for encouragement
Hi Neha,
very helpful..nice..
Thanks
Balaji
Thanks Bala
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Hey William , i have seen your document and it is really very helpful. Keep sharing
and thank you for including this 
HI Neha,
Useful info... thanks 4 sharing..
Thank Subbarao
Great piece of Information...
Thanks for your efforts and for sharing Neha..Thanks Henry for your corrections as well.
thanks Yuvraj
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.
e.g:
See: http://scn.sap.com/docs/DOC-29522
NMG
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..
Warm Regards,
Manny
There are many other Constants for formatting which changes as per Preferred Viewing Locale. Such as:
STANDARD
SCIENTIFIC
BOOLEAN
FULL_DATE
LONG_DATE
DATE
SHORT_DATE
TIME
SHORT_TIME
DATE_TIME
SHORT_DATE_TIME
INPUT_DATE
INPUT_TIME
INPUT_DATE_TIME
The following table explains it:
Thanks for adding to the above information!
Thanks for sharing.
Hi Neha,
Nice one doc.
Thnaks for sharing
Regards
Waseem
thanks !
nice document!
Hi,
Very helpful document.
Regards,
Mithun
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!
Thank you..!!
Helpful...thanks.
hi,
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.
Regards,
Balaji
Hi.
mm: Minutes and MM: Month.
thanks S
Thanks Santosh...
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