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.
@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
What is the last line, [Column6]@row?
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))
=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))
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
