How to use formulae to change date and time formats in WebIntelligence

[Former Member]

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.