Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-12-2018, 07:31 AM
GG@Excel GG@Excel is offline stop date calculation when a criteria is met Windows 10 stop date calculation when a criteria is met Office 2013
Novice
stop date calculation when a criteria is met
 
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 stop date calculation when a criteria is met Windows 7 64bit stop date calculation when a criteria is met Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 12-12-2018, 09:30 AM
ArviLaanemets ArviLaanemets is offline stop date calculation when a criteria is met Windows 8 stop date calculation when a criteria is met Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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 stop date calculation when a criteria is met Windows 10 stop date calculation when a criteria is met Office 2013
Novice
stop date calculation when a criteria is met
 
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, 9 views)
Reply With Quote
  #5  
Old 12-13-2018, 11:46 PM
ArviLaanemets ArviLaanemets is offline stop date calculation when a criteria is met Windows 8 stop date calculation when a criteria is met Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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, 10 views)
Reply With Quote
  #6  
Old 12-14-2018, 02:12 PM
GG@Excel GG@Excel is offline stop date calculation when a criteria is met Windows 10 stop date calculation when a criteria is met Office 2013
Novice
stop date calculation when a criteria is met
 
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 stop date calculation when a criteria is met Windows 8 stop date calculation when a criteria is met Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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
stop date calculation when a criteria is met 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
stop date calculation when a criteria is met Date field - future date calculation + only business days neon4 Word 7 01-21-2016 02:21 PM
stop date calculation when a criteria is met Date Calculation Lights Excel 5 04-18-2012 04:31 AM
calculation of start date ketanco Project 1 02-29-2012 07:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft