#1
|
|||
|
|||
stop date calculation when a criteria is met
hello, i am using excel built in function to calculate the elapsed date between today and a specified date in a cell. i want to stop date calculation when a criteria is met in another cell. any excel built in function to use in this case? i am using excel 2013. thanks! |
#2
|
||||
|
||||
Hi and welcome
perhaps post a sample sheet with some data and expected results ? ( no pics please)
__________________
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
|
|||
|
|||
You calculate time difference in days? So include it into MAX() function (or MIN(), depending on sign of calculated difference). Something like
Code:
=MAX(Date2-Date1, 30) |
#4
|
|||
|
|||
Thank you for your reply, attached sheet will help clarifying it.
|
#5
|
|||
|
|||
With the table as you have it, it is possible only using VBA code checking every entry made into sheet, was it in column Status of table or not, was the entered value "Open" or "Closed", what was content in cell in Aged column in same row (formula or value), and depending on result of those checks, leaves the content of cell in column Aged unchanged, or replaces it with current value in cell.
When you want to do it with worksheet formula, then you must have a column, where you enter 'Closed date' (Look in attached file - I left Status field there, but now is no need for it anymore - when the ClosedDate is not empty and is less or equal as today's date, the case is closed, otherwise it is open). |
#6
|
|||
|
|||
Thank you Arvilaanemets. case is solved!. but still need [status] for filtering purpose.
|
#7
|
|||
|
|||
You can have it calculated - no need for manually editing Status column. And of-course to avoid any confusion at data entry, move the Status column to rightmost position in table.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Age calculation with date and DOB fields | Xaand | Word | 9 | 09-29-2023 11:38 AM |
Excel Date Calculation | artlk | Excel | 3 | 11-20-2017 09:55 AM |
Date field - future date calculation + only business days | neon4 | Word | 7 | 01-21-2016 02:21 PM |
Date Calculation | Lights | Excel | 5 | 04-18-2012 04:31 AM |
calculation of start date | ketanco | Project | 1 | 02-29-2012 07:01 AM |