Formula - Dates

Hey all,

We are using smartsheet as a catch-all tool. I am working on creating a Desk Hoteling booking calendar for folks and would love to see if anyone has done something similar in the past. I'd like to use a formula from a source sheet that autofills information like dates.

An example: Sue sits in office 318A on Tuesdays, Wednesdays, Thursdays. Is there a formula that would flag all dates falling on Tuesdays, Wednesdays, Thursdays?

Find more posts tagged with

Accepted answers

All comments

NickStaffordPM

I am a little unclear on what the formula will need to do, but I can imagine setting up a third party colum with the WEEKDAY function. That way, if Sue sat at the deck on Tues Wed and Thurs, then when the column stated 2,3 or 4, the conditional formatting could be set tp black or grey out the cell. Some combination of these techniques should give you what you are looking for, although ou may need to set up a ton of conditional formatting rules.

Let me know if you have any other questions!

Lynn.McEnaney

Hey! I was hoping for a formula to put on a Source Sheet that says: "Sue sits at 318A on Tuesdays, Wednesdays, Thursday- autofill the calendar dates for all Tuesdays, Wednesdays, and Thursdays"

I have yet to find a solution but for the form itself I have people selecting a date on the calendar and put a formula in the back end that pulls in the Day of the Week that calendar date falls on: =IFERROR(IF(WEEKDAY(Date@row) = 1, "Sunday", IF(WEEKDAY(Date@row) = 2, "Monday", IF(WEEKDAY(Date@row) = 3, "Tuesday", IF(WEEKDAY(Date@row) = 4, "Wednesday", IF(WEEKDAY(Date@row) = 5, "Thursday", IF(WEEKDAY(Date@row) = 6, "Friday", IF(WEEKDAY(Date@row) = 7, "Saturday"))))))), "")

The weekday function returns a number for the day of the week and this just fills in the text which will be visually helpful

NickStaffordPM

Allowing for Dynamic drop downs is not something Smartsheet is built for at this time, so reducing the available desks to be selected once they are booked is not easily done by any stretch. Maybe someone with more experience than I will be able to comment on this!

Otherwise you could ditch the form, have people fill the sheet out directly, and use conditional formatting to show which desks are open on which days..

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.