-
How can I subtract a changing number of days from a date?
Hi there, I have a "Due Date" column and an "Order By Date" column. The Order By needs to reflect a date that is the Due Date minus our lead time (reflected in a third column and listed as a number of days). The issue is that the lead times change and vary from product to product. So I'm trying to create a formula that…
-
Is there a way to combine these two formulas? IF and NETDAYS, use of TODAY function
I would like to return a value based on the total number of Medicare Days used prior to admission, plus the total days elapsed since admission to calculate day 100, or benefit exhaust date. I've worked out the individual pieces but I'm having trouble combining the two. In the Days Remaining Column, I have this: =100 -…
-
Formula that changes status symbol based on check box checked/un-checked
I'm trying to create a formula that changes my status column to the red or green symbols. I looked at some examples on the site, but wasn't able to get it to work on my sheet. I was trying to set up a formula around the following parameters.. If check box is checked then green If check box is un-checked then red I have…
-
Networkday Formula Not Working Correctly
I'm using the networkday formula to gather the number of working days between two dates and then dividing that number by 5 to get the total number of weeks. However, the formula doesn't seem to provide the correct answer unless I change the dividing number to 4.7 (no idea why that would work since there's 5 days in a…
-
sum of parent rows only, below parent and child rows
Itemized budget rows, which are the sum of child rows. All data entered in child rows and totaled in the parent row. At bottom of page, I want to total the parent rows only. Any way to do this? Screen grab attached.
-
Status should = "Pending" if the cell is null
I have a sheet that looks for errors in a field, then populates a status if it finds those errors. For example: =IF(LEN(Body@row) < 1, "RED", IF(OR(CONTAINS("Rejected: 0", Body@row)) = true, "Green", "Yellow")) However, if the cell is null, it reads "#INVALID VALUE". In addition to the formula above, I would like to…
-
Week day
Hi good morning, I would like to know how I can get the day of the week in full. Is there any formula? I used the weekday but juts give me the number. Thanks
-
Trying to get some formulas to work to make a month timeline
I have a number of sheets that I am relaying on using this timeline sheet for. This is for a commission workplace and individual commissions. In the core sheet, I track the date of sale. Which that gives me a YEAR / Month. Then, I want to assign it a Current Month, Last month, 2 months ago, and so on. My goal was to…
-
What is the correct formula to count a category type with specific date ranges?
Hi! We are trying to count media types (per each specific media category) based on specific completion dates (01/01/2023 through 03/31/2023). What formula should we use to make this work? Any help is much appreciated! We have tried this one without luck: =COUNTIFS({Media Type}, = "Design", {Completion Date}, >= DATE(2023,…
-
Variable in Cross Sheet Reference
Hi All I have created 5 different cross sheet references, that refer to a range in 5 different sheets, these are named: {Year1} {Year2} {Year3} {Year4} {Year5} On a separate sheet I have a formula in one column [Invoice Year] that returns 1,2,3,4,5 based on what year of the project the invoice falls in The formula is…
