Skip to Content

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.

To report this post you need to login first.

66 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
  2. Ryan White

    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

    (0) 
  3. 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
    (0) 
  4. 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

    (0) 
  5. Sebastian Royer

    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.

    (0) 
  6. Ariel Batista

    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

     

    (0) 

Leave a Reply