Hello
OBJECTIVE:
Best method for simple, basic & concise way to calc:
DATE DIFFERENCE between 2 DATES displayed in:
YEAR - MONTH - DAY - HOUR - MIN
QUESTION:
Surely there must be a better way of calculating DATE DIFFERENCES of:
2 DATE:TIME CELLS in XL 2021 and/or 365 to calc, other then using DATEDIF? Unless it a much abbreviated version, then old layout..
So far best suggestion is CONCATE DATEIF, which is @ btm of this post, all in 1 cell visible to, not all folded under in cascading lines...
Please see attached Spreadsheet & Screen-Shot for clarification.
- DATES SUBTRACTED, but is incorrect with current FORMULA & FORMAT CELLS, yes I did try BRACKETS on HOURS… [HH] but was not right…either..!
=B1-A1
FORMAT CELLS CUSTOM:
yy "Yr" m "Mth" d "Day" hh:mm
See CELL E5 in RED for ERROR, & compare with correct DATE+TIME calc in F3.
- DATEDIF I recognise exists & probably the long term solution, but it is very very REPETITIVE, re-quoting CELLS REFs again & again, and when strung concertina together in 1CELL, it is complicated to follow, hence fraught with likely human error when used elsewhere, appreciate you might say…this is the way to do it & want u want to get it all in 1 and to display in 1 cell string together, or add it together from separates is easy to edit. E.G:
A.
1 CELL: F13
=DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"y")&" Year "&DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"ym")&" Mth "&INT(DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"md")/7)&" Wk "&MOD(DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"md"),7)&" Day "&TEXT(B1-A1,"h"" Hr "" m"" Min "" s"" Sec""")
Or
B.
SEPARATE CELLS:
YEAR: CELL E10
=DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"y")&" Year "
MTH: CELL F10
=DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"ym")&" Mth "
WK: CELL G10
=INT(DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"md")/7)&" Wk "
DAY: CELL H10
=MOD(DATEDIF(A1,B1 -(MOD(A1,1)>MOD(B1,1)),"md"),7)&" Day "
HOUR:MIN: CELL I10
=TEXT(B1-A1,"h"" Hr "" m"" Min """)
ADD TOGETHER: CELL F11
=E10&F10&G10&H10&I10
- CONCATE DATEDIF
=CONCAT(DATEDIF(A1,B1,{"y","ym","md"})&{" Year "," Mth "," Day "})&TEXT(B1-A1,"H""Hr"" M""M""")
And appreciate my perspective if was way for SIMPLE/BASIC SUBRTRACT to calc correct that would be best, if not, choice between the original DATEDIF separates or the suggested CONCATE DATEDIF is prob best…
But realistically surely in this day & age, there must be a better way or ways!
Cheers Stephan
CROSS THREADS?:
UPDATE: Best abbreviated DATEIF so far...
=CONCAT(DATEDIF(A1,B1,{"y","ym","md"})&{" Year "," Mth "," Day "})&TEXT(B1-A1,"h\Hr m\M")
So... CONCATE DATEIF is probably as good as it gets, unless some of you, an XL GENIUS have better ideas? But what about a simple/basic subtract..???
Subtract: B1-A1
FORMAT CELLS CUSTOM: yy "Yr" m "Mth" d "Day" hh:mm
But DATE is in ERROR. Partially right / Partially wrong...NUMBER of DAYS is WRONG!
What would be right, tried [HH] and after some humming (mmm0, turns out that not right... either..
Cheers
