Calculate difference between two dates (with time)

users
#21

I was right about it not handling some dates, but was more complex than I
though. Put in a date that was 1 second short of a year, and it was bad.

This seems to resolve the issue. Might have more rows than needed, Since
sometimes it has the correct results before the final row.
Again, pasted formulas to text editor, and change " " to _ and \t to ;

Date_1;Date_2;Years;Month;Day;Hour;Minute;Second
04/11/1960_07:42:00;04/11/1961_07:41:59;"=YEAR(B21)-YEAR(A21);"=MO
NTH(B21)-MONTH(A21);"=DAY(B21)-DAY(A21);"=HOUR(B21)-HOUR(A21);
"=MINUTE(B21)-MINUTE(A21);"=SECOND(B21)-SECOND(A21)
;;"=IF(D21<0,C21-1,C21);"=IF(D21<0,D21+12,D21)-(E21<0);"=IF(E21<0,DA
Y($B$2),E21)-(F21<0);"=IF(F21<0,F21+24,F21)-(G21<0);"=IF(G21<0,G21+6
0,G21);"=IF(H21<0,H21+60,H21)
;;"=IF(D22<0,C22-1,C22);"=IF(D22<0,D22+12,D22)-(E22<0);"=IF(E22<0,DA
Y($B$2),E22)-(F22<0);"=IF(F22<0,F22+24,F22)-(G22<0);"=IF(G22<0,G22+6
0,G22);"=IF(H22<0,H22+60,H22)
;;"=IF(D23<0,C23-1,C23);"=IF(D23<0,D23+12,D23)-(E23<0);"=IF(E23<0,DA
Y($B$2),E23)-(F23<0);"=IF(F23<0,F23+24,F23)-(G23<0);"=IF(G23<0,G23+6
0,G23);"=IF(H23<0,H23+60,H23)
;;"=IF(D24<0,C24-1,C24);"=IF(D24<0,D24+12,D24)-(E24<0);"=IF(E24<0,DA
Y($B$2),E24)-(F24<0);"=IF(F24<0,F24+24,F24)-(G24<0);"=IF(G21<0,G21+6
0,G21);"=IF(H24<0,H24+60,H24)
;;"=IF(D25<0,C25-1,C25);"=IF(D25<0,D25+12,D25)-(E25<0);"=IF(E25<0,DA
Y($B$2),E25)-(F25<0);"=IF(F25<0,F25+24,F25)-(G25<0);"=IF(H21<0,G25-1,
G25);"=IF(H25<0,H25+60,H25)
;;"=IF(D26<0,C26-1,C26);"=IF(D26<0,D26+12,D26)-(E26<0);"=IF(E26<0,DA
Y($B$2),E26)-(F26<0);"=IF(F26<0,F26+24,F26)-(G26<0);"=IF(H22<0,G26-1,
G26);"=IF(H26<0,H26+60,H26)
;;"=IF(D27<0,C27-1,C27);"=IF(D27<0,D27+12,D27)-(E27<0);"=IF(E27<0,DA
Y($B$2),E27)-(F27<0);"=IF(F27<0,F27+24,F27)-(G27<0);"=IF(H23<0,G27-1,
G27);"=IF(H27<0,H27+60,H27)

               users@global.libreoffice.org
Date sent: Sat, 14 Jul 2018 13:12:44 +1000
between two dates time)
Priority: normal

#22

Think this gets the job done as I see it, but it is a somewhat complex process.
Did the work at top of spreadsheet, but copied it a couple times, and then
copied it to show formulas.

Pasted into text editor, and changed spaces to _ and \t to ;

Date_1;Date_2;Years;Month;Day;Hour;Minute;Second
04/11/1960_07:42:00;07/14/2018_12:58:15;"=YEAR(B21)-YEAR(A21);"=MO
NTH(B21)-MONTH(A21);"=DAY(B21)-DAY(A21);"=HOUR(B21)-HOUR(A21);
"=MINUTE(B21)-MINUTE(A21);"=SECOND(B21)-SECOND(A21)
;;"=IF(D21<1,C21-1,C21);"=IF(D21<0,D21+12,D21);;;;
;;"=IF(D21<1,C21-1,C21);"=IF(E21<0,D22-1,D22);"=IF(E21<0,DAY(B21),E21
);;;
;;"=C23;"=D23;"=IF(F21<0,E23-1,E23);"=IF(F21<0,F21+24,F21);;
;;"=C24;"=D24;"=E24;"=IF(G21<0,F24-1,F24);"=IF(G21<0,G21+60,G21);
;;"=C25;"=D25;"=E25;"=F25;"=IF(H21<0,G25-1,G25);"=IF(H21<0,H21+60,H2
1)

Has 8 columns, and calcs the differences in second row. Other rows adjust
values if next column resulted in negative value. Probable would need a little
more work, since if the top value is 0 or 1 in some cases might require further
changes? Will have to look at it more, but would do most.

between two dates time)
Date sent: Fri, 13 Jul 2018 15:11:58 +0200

#23

I was right about it not handling some dates, but was more complex than I
though. Put in a date that was 1 second short of a year, and it was bad.

This seems to resolve the issue. Might have more rows than needed, Since
sometimes it has the correct results before the final row.
Again, pasted formulas to text editor, and change " " to _ and \t to ;

Date_1;Date_2;Years;Month;Day;Hour;Minute;Second
04/11/1960_07:42:00;04/11/1961_07:41:59;"=YEAR(B21)-YEAR(A21);"=MO
NTH(B21)-MONTH(A21);"=DAY(B21)-DAY(A21);"=HOUR(B21)-HOUR(A21);
"=MINUTE(B21)-MINUTE(A21);"=SECOND(B21)-SECOND(A21)
;;"=IF(D21<0,C21-1,C21);"=IF(D21<0,D21+12,D21)-(E21<0);"=IF(E21<0,DA
Y($B$2),E21)-(F21<0);"=IF(F21<0,F21+24,F21)-(G21<0);"=IF(G21<0,G21+6
0,G21);"=IF(H21<0,H21+60,H21)
;;"=IF(D22<0,C22-1,C22);"=IF(D22<0,D22+12,D22)-(E22<0);"=IF(E22<0,DA
Y($B$2),E22)-(F22<0);"=IF(F22<0,F22+24,F22)-(G22<0);"=IF(G22<0,G22+6
0,G22);"=IF(H22<0,H22+60,H22)
;;"=IF(D23<0,C23-1,C23);"=IF(D23<0,D23+12,D23)-(E23<0);"=IF(E23<0,DA
Y($B$2),E23)-(F23<0);"=IF(F23<0,F23+24,F23)-(G23<0);"=IF(G23<0,G23+6
0,G23);"=IF(H23<0,H23+60,H23)
;;"=IF(D24<0,C24-1,C24);"=IF(D24<0,D24+12,D24)-(E24<0);"=IF(E24<0,DA
Y($B$2),E24)-(F24<0);"=IF(F24<0,F24+24,F24)-(G24<0);"=IF(G21<0,G21+6
0,G21);"=IF(H24<0,H24+60,H24)
;;"=IF(D25<0,C25-1,C25);"=IF(D25<0,D25+12,D25)-(E25<0);"=IF(E25<0,DA
Y($B$2),E25)-(F25<0);"=IF(F25<0,F25+24,F25)-(G25<0);"=IF(H21<0,G25-1,
G25);"=IF(H25<0,H25+60,H25)
;;"=IF(D26<0,C26-1,C26);"=IF(D26<0,D26+12,D26)-(E26<0);"=IF(E26<0,DA
Y($B$2),E26)-(F26<0);"=IF(F26<0,F26+24,F26)-(G26<0);"=IF(H22<0,G26-1,
G26);"=IF(H26<0,H26+60,H26)
;;"=IF(D27<0,C27-1,C27);"=IF(D27<0,D27+12,D27)-(E27<0);"=IF(E27<0,DA
Y($B$2),E27)-(F27<0);"=IF(F27<0,F27+24,F27)-(G27<0);"=IF(H23<0,G27-1,
G27);"=IF(H27<0,H27+60,H27)

               users@global.libreoffice.org
Date sent: Sat, 14 Jul 2018 13:12:44 +1000
between two dates time)
Priority: normal

#24

​<...>

«Various projects such as Ubuntustarted using a "Code of Conduct" to
welcome people in One of the crucial
jobs of this mailing list is to be welcoming instead of judgmental.»

Exactly....

Henri​

#25

​<...>

«Various projects such as Ubuntustarted using a "Code of Conduct" to
welcome people in One of the crucial
jobs of this mailing list is to be welcoming instead of judgmental.»

Exactly....

Henri​