Skip to Content
Author's profile photo Former Member

Using RelativeDate to add Hours to an TimeStamp

Hello All,

During recent search I found that some of the posts are requested to add/Subtract Hours from a TimeStamp or manually changing the Time Zones at report Level, unfortunately we do not have a direct way Like AddHours(). So I am explaining below a method that can be useful in such cases.

Let us experiment this using CurrentDate(). Now a simple rule is that When using RelativeDate() function it needs the number of days that you want to add or subtract but, we need for Hours.

So the twist is that if we divide a day in hours we have 24 Hours now if we want x no of hours to be added or Subtracted then we just need to take that part from the day i.e 24 Hrs and add/subtract that form the Current Time.

For e.g. to get 1 Hrs added or subtracted we will take 1 part from 24 which is 1/24 similarly for 2 Hrs it will be 2/24 and so on.

For half hour it would be 0.5/24.

Lets see how it works at report level.


So as you can see we have two columns Current Date & Time and Modified Date & Time. As we can notice that Modified Date & Time is 1 hr behind the Current Date & Time.

Here’s the code how I have handled it at Report Level.

[Current Date & Time]=FormatDate(CurrentDate();”MM/dd/yyyy hh:mm:ss a”)

[Modified Date & Time]=FormatDate(RelativeDate(CurrentDate();-(1/24));”MM/dd/yyyy hh:mm:ss a”)

If you notice the underlined part of Modified Date & Time it shows how we have deducted 1 Hr from the Current Date.

The above method can be useful when you want to convert a Local Time Zone to a different Time Zone at report level.

Please share your valuable inputs and comments if we have a better way to deal with it.



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Shila Brown
      Shila Brown

      Hi Niraj, See below pro-rate formula you had provided. It doesn't seem to recognize the prorated piece. It keeps giving me only Budget Amount everytime. I even created a variable for Prorated piece and used the If Userresponse to give me prorated variable else budget amount but still same.

      Your formula:

      =If UserResponse("Prompt Text")=FormatDate(CurrentDate();"Mmm yyyy") Then ([Budget AMT]/DayNumberOfMonth(LastDayOfMonth(CurrentDate())))*DayNumberOfMonth(CurrentDate())

      Else [Budget AMT]

      This formula above was provided by Niraj in old forum which am cut off and NO Support provided here.

      I need help because that formula only fulfills second condition and ignores the first one. I really appreciate all the help here........



      Author's profile photo M Mohammed
      M Mohammed

      Hi Shila,

      Please post your question here on SBOP Web/Desktop Intelligence (client/server) Questions and Answers Page. I for one, am unable to understand what you're looking for. Is your question a continuation of a discussion from the SAP SCN, before it's migration to the new platform? Please post the whole question again and hopefully, people will answer.

      Mahboob Mohammed

      Author's profile photo Shila Brown
      Shila Brown

      Thanks Mahboob. Yes, it a continuation before the migration and now am lost with how to get throu. I will post to the link you replied. Thanks much!! Appreciate greatly.