Skip to main content

I get checkin and check our dates from an outside source, and need to add hours to the date field in order to set a specific time in a calendar.

The date with default time is stored as:


3/6/2020 12:00am

the variable for this field is {Start date}


I need to add 15 hours to this calendar entry to make the check-in time

3/6/2020 3:00pm


I use this formula, in order to be able to have the “15” a variable, I use CONCATENTATE to all for a variable in the future.


DATETIME_PARSE( DATETIME_FORMAT( DATEADD({Start date}, CONCATENATE(’+’,15),‘hours’) ,‘M/DD/YYYY h:mm’))


the result of this is…

3/6/2020 3:00am


it should be 3:00pm…

if I add another 12 hours - + 27h

the results are…

3/7/2020 3:00am


am I missing something… why can’t I get the time into the pm half of the day?


Why are you adding the CONTATENATE? I think you does not need it.

I agree, you should not need CONCATENATE in your function. CONCATENATE is used join static strings of text together. It is not necessary for computations with dates.


Also, since the formula results in a date, you do not need DATETIME_PARSE or DATETIME_FORMAT. Instead, set the formatting in the Formatting tab when you write the formula.


Another thing to look into is time zones. Make sure you have Use the same time zone (GMT) for all collaborators on both the original date field and the formula field. Often when things are a few hours off, there is a time zone issue.

I added the CONCATENATE, because the ‘15’ will be a variable from another field, and It was not working without it… I was able to figure-out another way to get this done… I was having too much trouble with the time zones… and getting the output to work…


Instead I actually truncate the time from the original fielf, by just writing out the date only.

add then just writing out the time from another field, then Parsing it into the date format…


DATETIME_PARSE(CONCATENATE( DATETIME_FORMAT({Start date},“YYYY-MM-DD”), " ",CheckinTime),“YYYY-MM-DD hh:mmA”)

It sounds like you came up with a very unique method of solving your problem.


If you ever decide you want to revisit this formula a different ways, please let us know more about the {checkinTime} field. That is a more complex situation than adding a fixed number of hours. The formula will depend on how the value is stored in {CheckinTime}. It is not clear if {CheckinTime} is a number, a duration, or a string, all of which would need to be handled differently.

Disclosure, I don’t really know what can be the effects of this but it works for me.


I have just multiplied by 1 the integer that I wanted use as the minutes (Integer comes from a lookup field)


Reply


Terms & Conditions