#1
|
|||
|
|||
Calculate due date with variable based on different start dates.
Hi,
I need a formula that will do the following: If the Declaration Date (column B) is on or before 3/22/2023, read the value in column I (POP End Date), subtract 60 days and return that value to column J (POP Ext Req Deadline). If the Declaration Date (column B) is after 3/22/2023, read the value in column I (POP End Date), subtract 90 days and return that value to column J (POP Ext Req Deadline). I created these two separate formulas: =IF(([@[Declaration Date]])<=3/22/2023), DATE(YEAR([@[POP End Date]]), MONTH([@[POP End Date]]), DAY([@[POP End Date]])-60))) =IF(([@[Declaration Date]])>3/22/2023), DATE(YEAR([@[POP End Date]]), MONTH([@[POP End Date]]), DAY([@[POP End Date]])-90))) I think they are close, as independent formulas, but Excel seems to be having problem reading the 2023 in the Declaration Date field. When I close the error message, the 2023 is highlighted in the formula bar, but I don’t know how to fix it. The column formatting is for Date, with structure used above. And, of course, those two sets of instructions need to be combined, with some kind of “ELSE” (or whatever language is relevant) instructions, but that’s where I get lost. Thanks |
#2
|
||||
|
||||
Try
Code:
=IF(([@[Declaration Date]])<=3/22/2023), DATE(YEAR([@[POP End Date]]), MONTH([@[POP End Date]]), DAY([@[POP End Date]])-60)),DATE(YEAR([@[POP End Date]]), MONTH([@[POP End Date]]), DAY([@[POP End Date]])-90))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
Code:
=[@[POP End Date]]-IF([@[Declaration Date]]<=DATE(2023,3,22),60,90) |
#4
|
|||
|
|||
I would have thought the code suggested by Pecoflyer would have worked, but I got the same error message, that then highlighted the "2023" in the code.
The code suggested by p45cal worked great! Last edited by wsnow; 04-25-2024 at 07:08 AM. |
#5
|
||||
|
||||
Code:
=IF([@[POP End Date]]="","",[@[POP End Date]]-IF([@[Declaration Date]]<=DATE(2023,3,22),60,90)) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculate date from two variable fields, with no #value error in empty rows | wsnow | Excel | 4 | 09-22-2023 05:55 AM |
Calculated Start Dates Based on Pre-scheduled Meetings | lcrevist | Project | 1 | 03-12-2016 10:32 AM |
Is this possible? - Calculate and Display the date range between 2 specific dates | Premier-UK | Excel | 2 | 08-18-2014 05:43 AM |
Excel to auto populate dates based on start and end | patidallas22 | Excel Programming | 9 | 04-21-2014 03:14 PM |
MS Project Start Dates not matching the Status Date | sjodom | Project | 3 | 12-04-2011 08:45 AM |