All Times

[DMan714]

trying to figure out how to calculate years, months, weeks, days, hours, minutes, and secs but every time I put in a formula for the weeks it all goes wonko.

 

[bosco_yip]

Submit a sanitized data file with criteria and expected result please!

 

[DMan714]

Hope this helps. I tried to explain as best I could.

 

[bosco_yip]

Try this solution with helper column,

Helper_Sum of Grp A & B >>
=($I3+$M3)/24+($J3+$N3)/24/60+($K3+$O3)/24/60/60

YEARS >>
=INT(G3/365)

MONTHS >>
=INT(MOD(Q3,365)/30)

WEEKS >>
=INT(INT(Q3-(INT(Q3/30)*30))/7)

>>>>>>>>>>>>>>

SECCONDS >>

=((K3+O3)/60-INT((K3+O3)/60))*60

 

[p45cal]

To simplify 3 of @bosco_yip 's formulae:
for hours, instead of:
=INT(MOD(Q3-(INT(Q3/30*30)),24)*24)
try:
=HOUR(Q3)

for minutes, instead of:
=INT((MOD(Q3-(INT(Q3/30*30)),24)*24-INT(MOD(Q3-(INT(Q3/30*30)),24)*24))*60)
try:
=MINUTE(Q3)

andfor seconds, instead of:
=((K3+O3)/60-INT((K3+O3)/60))*60
try:
=SECOND(Q3)

You could do something similar with years, months, weeks and days but this would end up being based on month lengths as if dates started 1 Jan 1900 which is not what was asked for.

ps. as an aside, isn't INT(Q3/30*30) in the above formulae the same as INT(Q3) ?

 

[bosco_yip]

To simplify 3 of @bosco_yip 's formulae:
for hours, instead of:
=INT(MOD(Q3-(INT(Q3/30*30)),24)*24)
try:
=HOUR(Q3)

for minutes, instead of:
=INT((MOD(Q3-(INT(Q3/30*30)),24)*24-INT(MOD(Q3-(INT(Q3/30*30)),24)*24))*60)
try:
=MINUTE(Q3)

andfor seconds, instead of:
=((K3+O3)/60-INT((K3+O3)/60))*60
try:
=SECOND(Q3)

You could do something similar with years, months, weeks and days but this would end up being based on month lengths as if dates started 1 Jan 1900 which is not what was asked for.

ps. as an aside, isn't INT(Q3/30*30) in the above formulae the same as INT(Q3) ?

Thank you p45cal for your advice.

I herewith attached my revised shorten formula solution file.

Of which inclusive single formula and separated formulae with Defined names.