Skip to Content
Author's profile photo Former Member

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 :

formating of time.PNG

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.

Assigned Tags

      66 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo seshu P
      seshu P

      Hi,

       

      Helpful Info , thanks For Sharing.

       

      Regards,
      Seshu.P

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Seshu for encouragement

      Author's profile photo CH Raman
      CH Raman

      Thanks for sharing the useful info.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      thanks Raman 

      Author's profile photo Henry Banks
      Henry Banks

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Henry ,

       

      i will implement the change soon , thanks for your suggestion

       

      Regrads,

      Neha.

      Author's profile photo Former Member
      Former Member

      Superb Neha.. Keep it up!

       

      Regards,

      Shyam Alok

      Author's profile photo Former Member
      Former Member

      its Good `````

       

      -Bhaskar

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Bhaskar

      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      I have started working on WebI lately so these tips and tricks will definitely be helpful.

       

      Thanks for sharing this info.

       

      Regards,

      AL

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks a lot Anshu 

      Author's profile photo sampath guntha
      sampath guntha

      really helpfull.....

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Sampath

      Author's profile photo Former Member
      Former Member

      Very informative, it can help beginners to start with webi reports ..

       

      Regards

      Shabnam

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Shabnam

      Author's profile photo Former Member
      Former Member

      hey its good..small but usefull tips..

      thanks for sharing...!!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Prerna

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      simply superb

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Krishna 

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Very useful information keep sharing.......

       

      Regards,

      SM

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Santendra

      Author's profile photo Former Member
      Former Member

      needful information, thanks for sharing:)

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Sudarshan

      Author's profile photo Former Member
      Former Member

      Good information Neha.. Keep posting!!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Laxmi for the encouragement

      Author's profile photo Thejas Krishnamoorthi
      Thejas Krishnamoorthi

      Informative!!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Thejas

      Author's profile photo Former Member
      Former Member

      Good document.....Helpful

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Chandra

      Author's profile photo wang zhanhui
      wang zhanhui

      Neha Kumari,You are an angel .

      What I need

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Wang

      Author's profile photo Former Member
      Former Member

      Smart solution steps for smart developers 🙂

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks a lot Muthukumar for encouragement 

      Author's profile photo Former Member
      Former Member

      Hi Neha,

       

      very helpful..nice..

       

       

      Thanks

       

      Balaji

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Bala

      Author's profile photo WILLIAM MARCY
      WILLIAM MARCY

      Great Webi trick !

       

       

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

       

       

      William

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey William , i have seen your document and it is really very helpful. Keep sharing and thank you for including this

      Author's profile photo Former Member
      Former Member

      HI Neha,

       

      Useful info... thanks 4 sharing..

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank Subbarao

      Author's profile photo Former Member
      Former Member

      Great piece of Information... Thanks for your efforts and for sharing Neha..Thanks Henry for your corrections as well.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      thanks Yuvraj 

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ryan ,

       

      Thanks for this information

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Another parameter worth noting is the "INPUT_DATE_TIME" / "INPUT_DATE" parameter, which formats the date per locale.

       

      e.g:

      =FORMATDATE([Date Object];"INPUT_DATE_TIME")

       

      See: http://scn.sap.com/docs/DOC-29522

       

      NMG

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      thanks Neil nice piece of info

      Author's profile photo Vinay Kumar
      Vinay Kumar

      Thanks Neha. I get ample of requests to address these sorts of date formats.. This document has been a great help..

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks a lot Vinay

      Author's profile photo Harshil Joshi
      Harshil Joshi

      Thanks for sharing. Good work.....

      ___

       

      Related with my post Date Variables in Web Intelligence

      Author's profile photo Manohar Singh
      Manohar Singh

      Thank you, these are really cool formatting tips for beginners..

       

      Warm Regards,

      Manny

      Author's profile photo Arijit Das
      Arijit Das

      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:

       

      en_US de_DE zh_CN fr_FR en_GB nl_NL
      Flag : Boolean Default 1 1 1 1 1 1
      =FormatNumber([Flag];"BOOLEAN") true wahr 真 vrai true waar
      Number Default 1,234.57 1.234,57 1,234.57 1 234,57 1,234.57 1.234,57
      =FormatNumber([Number];"STANDARD") 1,234.57 1.234,57 1,234.57 1 234,57 1,234.57 1.234,57
      =FormatNumber([Number];"SCIENTIFIC") 1.2345678E3 1,2345678E3 1.2345678E3 1,2345678E3 1.2345678E3 1,2345678E3
      Date Default 1/30/14 30.01.14 14-1-30 30/01/14 30/01/2014 30-01-14
      =FormatDate([Date];"FULL_DATE") Thursday, January 30, 2014 Donnerstag, 30. Januar 2014 2014年1月30日星期四 jeudi 30 janvier 2014 Thursday, 30 January 2014 donderdag 30 januari 2014
      =FormatDate([Date];"LONG_DATE") January 30, 2014 30. Januar 2014 2014年1月30日 30 janvier 2014 30 January 2014 30 januari 2014
      =FormatDate([Date];"DATE") Jan 30, 2014 30.01.2014 2014-1-30 30 janv. 14 30 Jan 2014 30 jan 2014
      =FormatDate([Date];"SHORT_DATE") 1/30/14 30.01.14 14-1-30 30/01/14 30/01/2014 30-01-14
      =FormatDate([Date];"INPUT_DATE") 1/30/2014 30.01.2014 2014-1-30 30/01/2014 30/01/2014 30-1-2014
      =FormatDate([Date];"SHORT_TIME") 1:16 PM 13:13 下午1:14 13:08 13:14 13:16
      =FormatDate([Date];"TIME") 1:16:41 PM 13:13:08 下午01:14:58 13:08:03 13:14:06 13:16:09
      =FormatDate([Date];"INPUT_TIME") 1:16:41 PM 13:13:08 13:14:58 13:08:03 13:14:06 13:16:09
      =FormatDate([Date];"DATE_TIME") Jan 30, 2014 1:16:41 PM 30.01.2014 13:13:08 2014-1-30 下午01:14:58 30 janv. 14 13:08:03 30 Jan 2014 13:14:06 30 jan 2014 13:16:09
      =FormatDate([Date];"SHORT_DATE_TIME") 1/30/14 1:16 PM 30.01.14 13:13 14-1-30 下午1:14 30/01/14 13:08 30/01/2014 13:14 30-01-14 13:16
      =FormatDate([Date];"INPUT_DATE_TIME") 1/30/2014 1:16:41 PM 30.01.2014 13:13:08 2014-1-30 13:14:58 30/01/2014 13:08:03 30/01/2014 13:14:06 30-1-2014 13:16:09
      Author's profile photo Former Member
      Former Member

      Thanks for adding to the above information!

      Author's profile photo Former Member
      Former Member

      Thanks for sharing.

      Author's profile photo Wasem Hassan
      Wasem Hassan

      Hi Neha,

      Nice one doc.

      Thnaks for sharing

      Regards

      Waseem

      Author's profile photo Former Member
      Former Member

      thanks !

       

      nice document!

      Author's profile photo Former Member
      Former Member

      Hi,

       

      Very helpful document.

       

      Regards,

      Mithun

      Author's profile photo Former Member
      Former Member

      I think there is an error in the formula. You use DayNumberOfYear where I think it should be DayNumberOfMonth.

      Author's profile photo Former Member
      Former Member

      Thanks for sharing . I really appreciate time and effort!

      Author's profile photo Former Member
      Former Member

      Thank you..!!

      Author's profile photo Former Member
      Former Member

      Helpful...thanks.

      Author's profile photo Balaji Vivekanandan
      Balaji Vivekanandan

      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

      Author's profile photo Former Member
      Former Member

      Hi.

       

      mm: Minutes and MM: Month.

       

      thanks S

      Author's profile photo Balaji Vivekanandan
      Balaji Vivekanandan

      Thanks Santosh...

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      thank you for sharing

      Author's profile photo Former Member
      Former Member

      Thanks, very useful

      Author's profile photo Former Member
      Former Member

      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