•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Trying to make a 'Days Since' cell, that checks three other cells for dates (or any text), and outputs a number based on those cells.
Our take
Hello Excel community! I’m looking for assistance in creating a 'Days Since' column that calculates the number of workdays since the Last Action date, while considering three specific conditions. If the Last Action date is missing, the formula should reference the Submitted date instead. However, if there is any entry in the Completed column, the output should remain blank. I’ve made some progress, but I’m encountering formula errors and undesired outputs when dates are absent. Any guidance on refining this formula would be greatly appreciated!
Hey Excel community! I have three dates on my sheet for each line: the date submitted, the date last actioned and a completion date if completed.
I am trying to have a 'Days Since' column that outputs the number of workdays since the Last Action date. If there is no Last Action I want it to output the number of workdays since the Submitted date. BUT, if there is a date (or text) in the Completed column, I want it to be empty.
I have gotten it to output 'days since' the Last Action date,
=IF(ISNUMBER([@[Last Action]]), MAX(0, NETWORKDAYS([@[Last Action]] +1, TODAY() )), "") without including the Last Action date itself (the +1), and not being negative for stuff entered today (the MAX(0,). But my attempt to work from the Submit date, if there is no Last Action date haven't been as successful (mostly formula errors) or continuing to accrue days on stuff that has been completed, which I would prefer just be blank.
I made some progress that I think is kind of doing what I want, but any line without a date returns a large number that it throwing off my conditional formatting, and I think it is just a messy way of doing what I want:
=IF(AND([@[Last Action]]="", [@Completed]=""), MAX(0, NETWORKDAYS([@Submitted] +1, TODAY() )), IF(ISNUMBER([@[Last Action]]), MAX(0, NETWORKDAYS([@[Last Action]] +1, TODAY() )), "")) I was wondering how to make it be blank if there are no dates, and also if there is just a better way to do this..?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Trying to add different dates based on different cellsI posted yesterday, but realized there’s a bit more to it than I originally thought. I’ll have an example of what I tried at the bottom. So I need to have dates in one column, (I) reflect different dates based on dates in column, H (created_date), and also based on text in column, B (delayed) and dates in column, G (original_date). Column (I) will have due days of +90 days from column (H) if created on or after 3/1/26. If the date is before that, it needs due dates of column (G)-45 days. Lastly, if column (B) says “extended” or “extended x2” then column (I) needs to have a due date of column (G)-120 days as well. This is the formula I have right now and the issue I’m having is that it’s not populating the correct dates. The formula is input into column (I), and there are blanks sometimes which is addressed in the beginning of the formula. Again, any insight would help! Thank you! =switch([@[created_date]],0,””, [@[created_date]],+IF([@[created_date]]>=Date(2026,3,1),([@[original_dates]]-90),45)+IF([@[delayed]]=“extended”,([@[original_dates]]-120))+IF([@[delayed]]=“extended x2”,([@[original_dates]]-120))) submitted by /u/sadtefany [link] [comments]
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Conditional Formatting for Dates Missing Numbers (i.e. 3/11/202 instead of 3/11/2026)Does anyone have a formula for formatting dates that are missing numbers? I have created a complex tracker, but it cannot do its job if the user does not enter the date right. I know I cannot stop user error, but I am hoping that it could mitigate some of it if the cell turns bright red when they have done something wrong. I tried and the "ISDATE" function is not working for me. Additionally, I do have text in the document that I do not want formatted so I was playing around with =AND(ISNUMBER(A3),... as the beginning, but could not figure out an end. I also tried typing out an "example date" and having the formula reference that formatting and highlighting the incorrect dates, but I could not get that to work either. Any help is greatly appreciated, please let me know if you have any questions! https://preview.redd.it/r4fuw96cwfvg1.png?width=307&format=png&auto=webp&s=7c8f9db16ceb090d43fc9c8b2b278f32acbb7689 submitted by /u/ClassroomHairy9513 [link] [comments]
- Conditional formatting with formulasI am trying to create a spreadsheet to track ordered parts and their status, including their requested delivery date. What I'm trying to sort out, and failing at, is setting a conditional format so that when the requested delivery date is less than X days out (say 14 for now), I want it to highlight the cell red so I can check on the status of the order. This is my current rule configuration, which I set by selecting the column so it auto populates as it's filled in. https://preview.redd.it/n4g68xoihzsg1.png?width=409&format=png&auto=webp&s=6715ebba2e873e171fad3b862ff3a7d188b4b3a1 Inconveniently, it's doing the exact opposite of what I want: https://preview.redd.it/h7a7in7ohzsg1.png?width=294&format=png&auto=webp&s=1168efcd78872a5158cd045e6c3ea642c1328152 In this instance, ONLY 4/13 should be highlighted (I'm not worried about the top row at this point, I'll fix that after). I've even tried creating another column to do the math, and use that cell for the formula, and that's not working either. It seems like it should be straight forward, but I apparently lack the formatting skill. submitted by /u/Senior_Cheesecake155 [link] [comments]