-
Formula to return a value if today is in the same month as a cell
Hi, I am trying to create conditional formatting for a row if the month entered is the same as the current month. If the invoice month is July, and it is currently July, I would like to return a 1 in the Month Conditional Formatting column. Then, create a rule that turns the entire row a color if there is a 1 in the Month…
-
Adding Multiple Criteria to a COLLECT Function
I have a working formula gathering the MEDIAN of a group of numbers that fall within a date range used in a trend chart in a dashboard. I now want to add two additional criteria for cases when the dashboard user would like to calculate the MEDIAN but exclude certain values (referred to as 'HOLD' and 'Strategic' data) from…
-
Workday has bug?
Anybody used Workday function? It adjusts the date skipping holidays and weekend but I am observing a weird behavior-> it works absolutely perfect for current year but disregards next year's weekend and holiday. Tried two approaches but same result 1) Entered holidays in project settings 2) Creating a holiday column First…
-
Child Auto Numbering per Parent Row
Help! In a Time Crunch! I was wondering if anyone could help me. I am so thankful for Brian Richardson original post from 07/2024 showing the breakdown, I think it is the answer that I have been looking for. But I can't get the formula to work. Can you help me? Columns: Parent Formula I have: Child Number Formula I have: I…
-
Question about Workday Formula
Hi there - I'm looking for some assistance with a WORKDAY Formula. I am trying to: Look at a date in a cell - let's call that "Program Date" and add 12 days to that date Then reference another sheet that has a column of dates and pull back the earliest date (that's later than Program Date plus 12 days) Exclude holidays I…
-
Formula to Calculate Building Age from Opening Date
Hello All, I'm trying to create a formula that will automatically calculate the age of a building based off the Opening Date. I have two columns "Opening Date" and "Building Age". In the Building Age column I've tried a few formulas but keep receiving various errors. =TODAY-([Opening Date}@row) One thing to point out is…
-
COUNTIF Formula not returning correct values...
I am using a helper sheet to create a dashboard and want to find how many of each value is in a certain column in the source sheet, and it is returning the incorrect values. There are values such as Col A, Col B, Systems, etc. In this example I show I am using =COUNTIF({BT Migration Master Sheet Range 2}, "Col B") and it…
-
Sheet Summary formula COUNTIFS with two criteria in one column
I am trying to make a sheet summary formula to generate the total number of cells that are either blank or have Submitted within them. So far I have tried multiple different formulas and this is the one I am currently testing however it comes back as #UNPARSEABLE =COUNTIFS([Site Supervisor Approval]:[Site Supervisor…
-
max(startdate) function in Countifs.
Hi all, I am looking if someone cane help me in writing an expression, I am creating a calculation sheet in my smart sheet, I have a multiple teams (5 separate sheets) sheets with Project name, description, effort points, Status(dev, test, uat, done) weekstartdate and weekenddate In my calculation sheet, I have columns…
-
What is the function of number one at the end of this formula?
=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1) Thank you!
