-
Needing Help with an IF TODAY formula
After reading countless articles and trying countless combos, I still can't get this formula to work. I am trying to give a 30 day notice to an expiration day with a status column. =IF([TIPS Expiration]-TODAY()>30,"Green", IF[TIPS Expiration]-TODAY()<30,"Yellow"))
-
Budget v. Invoice Cost Codes
I have two Smartsheets. Sheet 1 is my budget broken down into various cost codes. Sheet 2 is a log of all my invoices with cost codes and amounts assigned to each invoice. I am trying to take the amount from each invoice and have them populate in my budget according to their cost codes. The budget amounts for each cost…
-
Error w/ Formula (#INVALID)
Hi Formula Friends - Can you help me identify what's going wrong with the following formula (Reference Cell: 'Count1'): =COUNTIFS([Work Related Exp]:[Work Related Exp], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2)) I was working a few days ago; however, something…
-
Email / Name reference formula help
Hi, I am trying to create a formula where an entered email address automatically populates the person's name associated with that email in an adjacent column (see below). The data is referenced from a master sheet of people's email/name contact information. I have this function in a different sheet and it works perfectly…
-
how to handle duplicate data from the source sheet when using vlookup?
Hi, I have a work schedule sheet (source/reference data) and shift schedule of employees is being logged there. Employees shift schedule can be night shift and day shift per employee. Now, I have another sheet (Control log sheet) where I am looking up the work schedule of each employee in a daily basis. When I am using the…
-
Formula to calculate date
Hello, I am trying to work backwards but I just don't get it. My event date is my constraint, I want some tasks to be completed x days prior to the event and Smartsheet to calculate the start date of the tasks automatically. For example, send agenda to attendees 2 months prior to the event. I've tried playing with the…
-
How to change the date selection on a Form based on time of day || Help!
Hello! I have a form that lets you select a "pick up date" but I wanted to see if based on the time of date they are filling out the form gray out some pick up date options. So for example if they are filling the form out at midnight on July 8th I don't want them to be able to select the pick up date as July 8th in the…
-
Cell Reference Within Same Sheet Not Working
Hi I want to reference column "Start Date" in the column "End Date". I want the formula in "End Date" to be =Start Date15+5 (add 5 days to start date). However, for some reason it is not working. I am using this in the Gantt tracker. Do you have any idea how can I make it work? I have noticed that I am not able to…
-
Countifs for a date range
Hello Team I need to calculate count of countries within the date range of request received . I am using below formula but its throwing invalid operation error, could u please correct me where I am making mistake. Current Formula: =COUNTIFS({Table 1 Range 1}, {Table 1 Range 30}, >=[Date Received From]32, {Table 1 Range…
-
Return a value based on looking at two cells
Hello SS Community, I am having a bit of formula writing block. I am trying to return a value based on looking at two cells. For example in the picture below. If Onboarding Complete Date is blank and I9 Complete Date is blank, change the OB Status column to 'Awaiting Talent Paperwork' If Onboarding Completed Date is…
