How do I add hours to Now() after converting it to a text value?

Question

I have a calculated field that shows the text value of the current hour, so I'm using:

=TEXT(Now(), "hh")

Unfortunately, it's showing this value in Pacific time, so I'd like to add 3 hours to display the value as if it were Eastern time. In the past, I've used a formula that adds hours to a date/time field like this:

=Now()+(3/24)

So I combined the syntax like this:

=TEXT(Now()+(3/24), "hh")

Unfortunately, it does not seem to work. I get some really odd 2 digit number and not sure what I'm doing wrong.

Can this be done?

Answer 1

As Now() returns a full formated date-time-stamp, I believe you should add hours by using divide a day by 24 hours by 60 minutes multiplied the amount of hours in minutes formula. In this case, it would be (1/24/60*180) as 3 hours = 180 minutes.

By the way, that should not require the use of =TEXT, so a complete calculcated column would be =Now()+(1/24/60*180).

Answer 2

You were on the right track,

A SharePoint DateStamp is the number of days since 1/1/1900

So 3 hours is indeed 3/24

=Text( Now()      , "m/d/y hh:mm" )   // now

=Text( Now()+3/24 , "m/d/y hh:mm" )   // 3 hours ahead

But you might have another problem!

now behaves the same as the infamous SharePoint Today

See: How to use Today and Me in Calculated column

it does not update!!!! SharePoint is not Excel

Because Formulas are only recalculated when the Item is updated
or when you change the Formula itself

Read and understand that one more time:

Because Formulas are only recalculated when the Item is updated
or when you change the Formula itself

That means:

With:

=Text( Now()+3/24 , "hh" )   // hours

If you wait an Hour and press F5 refresh in a View, the values are never updated

because the Formula executed an hour (or longer) ago
when you changed the Item or updated the Formula itself

See: How to use Today and Me in Calculated column