Total Number of days hours and minutes

[skorpion]

I'm looking for how many days hours and minutes elapse between today and 27/08/2020 17:00. Is there and formula where i can achieve this? The output should look like this:

0 Year, 1 Month, 7 Weeks, 50 Days, 1,207, Hours, 72,427 Minutes, and 4,345,620 Seconds

 

[Peter Bartholomew]

 

[skorpion]

Thanks

I like this

=+DATEDIF($A$2,$A$1,"Y")&" Years"&", "&DATEDIF($A$2,$A$1,"M")&" Months"&", "&ROUND(($A$1-$A$2)/7,0)&" Weeks"&", "&ROUND($A$1-$A$2,0)&" Days"&", "&TEXT(ROUND(24*($A$1-$A$2),0),"0,000")&" Hours"&", "&TEXT(ROUND(24*60*($A$1-$A$2),0),"0,000")&" Minutes"&", "& TEXT(ROUND(24*3600*($A$1-$A$2),0),"0,000")&" Seconds"

 

[Peter Bartholomew]

That would do it. If you have the CONCAT function available to you, you could also go for an array solution
= CONCAT( DATEDIF(date,today, {"Y";"M"} ) & {" Years, ";" Months, "} )
& CONCAT( INT((today-date)*{0.142857;1;24;1440;86400}) & {" Weeks, ";" Days, ";" Hours, ";" Minutes, ";" Seconds."} )
or if you know about named constants
= CONCAT(DATEDIF(date,today, {"Y";"M"}) & majorUnit ) & CONCAT(INT((today-date)*factor) & unit)
would get the formula on a single line.