Skip to Content

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.

Capture.jpg

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.

Regards

Niraj

To report this post you need to login first.

3 Comments

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

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

    PLEASE DO NOT DELETE MY MESSAGE – RATHER PROVIDE SUPPORT TO ASSIST ME HERE. THANKS!!!

     

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

        (0) 

Leave a Reply