Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-12-2018, 07:31 AM
GG@Excel GG@Excel is offline Windows 10 Office 2013
Novice
 
Join Date: Oct 2018
Posts: 3
GG@Excel is on a distinguished road
Question 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!
Reply With Quote
  #2  
Old 12-12-2018, 08:42 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,256
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
perhaps post a sample sheet with some data and expected results ? ( no pics please)
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 12-12-2018, 09:30 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 349
ArviLaanemets is on a distinguished road
Default

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)
Reply With Quote
  #4  
Old 12-13-2018, 11:42 AM
GG@Excel GG@Excel is offline Windows 10 Office 2013
Novice
 
Join Date: Oct 2018
Posts: 3
GG@Excel is on a distinguished road
Default

Thank you for your reply, attached sheet will help clarifying it.
Attached Files
File Type: xlsx BOOK_ONE.xlsx (9.1 KB, 3 views)
Reply With Quote
  #5  
Old 12-13-2018, 11:46 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 349
ArviLaanemets is on a distinguished road
Default

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).
Attached Files
File Type: xlsx BOOK_ONE.xlsx (9.0 KB, 4 views)
Reply With Quote
  #6  
Old 12-14-2018, 02:12 PM
GG@Excel GG@Excel is offline Windows 10 Office 2013
Novice
 
Join Date: Oct 2018
Posts: 3
GG@Excel is on a distinguished road
Default

Thank you Arvilaanemets. case is solved!. but still need [status] for filtering purpose.
Reply With Quote
  #7  
Old 12-15-2018, 04:38 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 349
ArviLaanemets is on a distinguished road
Default

Quote:
Originally Posted by GG@Excel View Post
...but still need [status] for filtering purpose.
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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
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
Age calculation with date and DOB fields Xaand Word 7 02-21-2014 09:15 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


All times are GMT -7. The time now is 07:14 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft