| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating the difference between two dates if two criteria are m
The criteria have two parameters: A. Value in column F matches one of the following three words: Open, On-going, or On-Hold and B. If todays date is greater than the date in column D Then calculate the number of days between todays date and date in column D using days The formula will be in Column G and The cell should have either a number (of days) or be blank. Thank you in advance for your help. A B C D E F G 22 Text 03/05/08 04/01/08 On-going 23 Text 03/05/08 03/31/08 04/03/08 Done 24 Text 03/05/08 03/05/08 03/06/08 Done 25 Text 03/05/08 03/15/08 03/06/08 Done 26 Text 03/05/08 04/15/08 04/01/08 Done 27 Text 04/07/08 04/15/08 Open 28 Text 04/07/08 04/11/08 Open 29 Text 04/07/08 04/09/08 04/09/08 Done 30 Text 04/07/08 04/30/08 Open 31 Text 04/07/08 04/07/08 04/07/08 Done 32 Text 04/07/08 04/15/08 04/11/08 Done 33 Text 04/07/08 04/11/08 04/09/08 Open 34 Text 04/07/08 04/11/08 Open |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating the difference between two dates if two criteria are m
Try this formula in G1 and copy on down
=IF(And(OR(F1="Open",F1="On-Going",F1="On-Hold"),Today()D1),Today()-D1,"") This is untested and just off the top of my head... so give it a try and let me know if there are any problems "Detroit David" wrote: The criteria have two parameters: A. Value in column F matches one of the following three words: Open, On-going, or On-Hold and B. If todays date is greater than the date in column D Then calculate the number of days between todays date and date in column D using days The formula will be in Column G and The cell should have either a number (of days) or be blank. Thank you in advance for your help. A B C D E F G 22 Text 03/05/08 04/01/08 On-going 23 Text 03/05/08 03/31/08 04/03/08 Done 24 Text 03/05/08 03/05/08 03/06/08 Done 25 Text 03/05/08 03/15/08 03/06/08 Done 26 Text 03/05/08 04/15/08 04/01/08 Done 27 Text 04/07/08 04/15/08 Open 28 Text 04/07/08 04/11/08 Open 29 Text 04/07/08 04/09/08 04/09/08 Done 30 Text 04/07/08 04/30/08 Open 31 Text 04/07/08 04/07/08 04/07/08 Done 32 Text 04/07/08 04/15/08 04/11/08 Done 33 Text 04/07/08 04/11/08 04/09/08 Open 34 Text 04/07/08 04/11/08 Open |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating the difference between two dates if two criteria a
Thank you it worked perfectly.
The top of your head is better than all of mine. Thanks again "akphidelt" wrote: Try this formula in G1 and copy on down =IF(And(OR(F1="Open",F1="On-Going",F1="On-Hold"),Today()D1),Today()-D1,"") This is untested and just off the top of my head... so give it a try and let me know if there are any problems "Detroit David" wrote: The criteria have two parameters: A. Value in column F matches one of the following three words: Open, On-going, or On-Hold and B. If todays date is greater than the date in column D Then calculate the number of days between todays date and date in column D using days The formula will be in Column G and The cell should have either a number (of days) or be blank. Thank you in advance for your help. A B C D E F G 22 Text 03/05/08 04/01/08 On-going 23 Text 03/05/08 03/31/08 04/03/08 Done 24 Text 03/05/08 03/05/08 03/06/08 Done 25 Text 03/05/08 03/15/08 03/06/08 Done 26 Text 03/05/08 04/15/08 04/01/08 Done 27 Text 04/07/08 04/15/08 Open 28 Text 04/07/08 04/11/08 Open 29 Text 04/07/08 04/09/08 04/09/08 Done 30 Text 04/07/08 04/30/08 Open 31 Text 04/07/08 04/07/08 04/07/08 Done 32 Text 04/07/08 04/15/08 04/11/08 Done 33 Text 04/07/08 04/11/08 04/09/08 Open 34 Text 04/07/08 04/11/08 Open |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating the difference between two dates if two criteria a
Glad it worked, must've been the red bull!
"Detroit David" wrote: Thank you it worked perfectly. The top of your head is better than all of mine. Thanks again "akphidelt" wrote: Try this formula in G1 and copy on down =IF(And(OR(F1="Open",F1="On-Going",F1="On-Hold"),Today()D1),Today()-D1,"") This is untested and just off the top of my head... so give it a try and let me know if there are any problems "Detroit David" wrote: The criteria have two parameters: A. Value in column F matches one of the following three words: Open, On-going, or On-Hold and B. If todays date is greater than the date in column D Then calculate the number of days between todays date and date in column D using days The formula will be in Column G and The cell should have either a number (of days) or be blank. Thank you in advance for your help. A B C D E F G 22 Text 03/05/08 04/01/08 On-going 23 Text 03/05/08 03/31/08 04/03/08 Done 24 Text 03/05/08 03/05/08 03/06/08 Done 25 Text 03/05/08 03/15/08 03/06/08 Done 26 Text 03/05/08 04/15/08 04/01/08 Done 27 Text 04/07/08 04/15/08 Open 28 Text 04/07/08 04/11/08 Open 29 Text 04/07/08 04/09/08 04/09/08 Done 30 Text 04/07/08 04/30/08 Open 31 Text 04/07/08 04/07/08 04/07/08 Done 32 Text 04/07/08 04/15/08 04/11/08 Done 33 Text 04/07/08 04/11/08 04/09/08 Open 34 Text 04/07/08 04/11/08 Open |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum the difference between dates given a certain criteria | Excel Worksheet Functions | |||
| calculating time difference | Excel Worksheet Functions | |||
| Calculating the difference between 2 dates and times | Excel Discussion (Misc queries) | |||
| Irregularity in calculating difference between two dates | Excel Discussion (Misc queries) | |||
| Calculating difference between two times | Excel Worksheet Functions | |||
![Detroit David[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
