Have dates respect plurality...

Chayes · #1 07-30-2018, 05:31 PM

Hi All

I use this formula to give the time elapsed form the date showing in B6

Code:

="("&DATEDIF(B6,TODAY(),"y")&" years "&DATEDIF(B6,TODAY(),"ym")&" months "&DATEDIF(B6,TODAY(),"md")&" days"&")"

It works perfectly well , but has certain anomalies. For example it will make Days , Month and Years plural when there may only be one of each involved.

EG 1 Days , 1 Months , 1 Years

Can someone suggest a modification of the formula to give respect to single months , days and years?

EG 1 Day , 1 month , 1 Year

Grateful for any advice.

xor · #2 07-30-2018, 11:35 PM

="("&DATEDIF(B6,TODAY(),"y")&IF(DATEDIF(B6,TODAY() ,"y")=1," year ","years")&DATEDIF(B6,TODAY(),"ym")&IF(DATEDIF(B6, TODAY(),"ym")=1," month "," months ")&DATEDIF(B6,TODAY(),"md")&IF(DATEDIF(B6,TODAY(), "md")=1," day "," days"&")")

Chayes · #3 07-31-2018, 06:05 AM

OK thanks for this. This resolves the issue first time.Much appreciated.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
X axis graph all dates when date column contains specific dates..possible ?	DBenz	Excel	2	03-28-2018 04:59 AM
Autofill dates - how can I get the same date and consecutive dates?	Exhale	Excel	3	04-05-2016 03:11 AM
Compare relative value with respect to specific column	PRA007	Excel Programming	2	01-26-2016 06:11 AM
Extract data based on pattern with respect to specific column	PRA007	Excel Programming	14	12-04-2015 04:32 AM
Changing Actual Finish dates changes original Finish dates	SomewhereinTX	Project	3	12-20-2013 10:25 AM