Time formula: how to add 4 hours to my appoint time column?

Hi,

I have a appointment time column and I would like to use a formula and add 4 hours to get the End time.

Is there an alternative way to do it like sort of formulas?

Kindly help, I need this to my report this week.


Tags:

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Benjo090

    Assuming the column App Time is of type Text - Try this formula:

    =IF(VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add> > 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add> - 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add>) + MID([App Time]@row, FIND(":", [App Time]@row), LEN([App Time]@row) - IF(LEN([App Time]@row) = 11, 4, 3)) + [Column6]@row

    So in your case <hours you want to add> = 4 so the formula would look like this:

    =IF(VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4 > 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4 - 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4) + MID([App Time]@row, FIND(":", [App Time]@row), LEN([App Time]@row) - IF(LEN([App Time]@row) = 11, 4, 3)) + [Column6]@row

    I hope that helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • What is the last line, [Column6]@row?

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Benjo090

    Apologies, that was a copy/paste error on my part. Here is the correct formula.


    =IF(VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add> > 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add> - 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add>) + MID([App Time]@row, FIND(":", [App Time]@row), LEN([App Time]@row) - IF(LEN([App Time]@row) = 11, 4, 3)) + + IF(VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + <hours you want to add> >= 12, IF(RIGHT([App Time]@row, 2) = "PM", "AM", "PM"), RIGHT([App Time]@row, 2))

    So in your case <hours you want to add> = 4 so the formula would look like this:

    =IF(VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4 > 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4 - 12, VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4) + MID([App Time]@row, FIND(":", [App Time]@row), LEN([App Time]@row) - IF(LEN([App Time]@row) = 11, 4, 3)) + IF(VALUE(LEFT([App Time]@row, FIND(":", [App Time]@row) - 1)) + 4 >= 12, IF(RIGHT([App Time]@row, 2) = "PM", "AM", "PM"), RIGHT([App Time]@row, 2))

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!