Forum Discussion
excel : Calculation problem between two sets of digits (hours and minutes)
I have two sets of numbers from a company-specific program where I work. f.i. 202107231659 and 202107231702. Each time the last 4 digits actually represent the hours and minutes. When I want to make the difference between the two the calculation makes the subtraction of the two digits (1702-1659 = 43) While I’d like it to calculate the difference in minutes between 17h02m et 16h59m = 3 minutes. Have a long list of numbers to treat (about 3500). Someone can help me ? Thanks in advance
gobertchristphe1968 You need to extract the last four digits of each number as a time, first. Perhaps the attached workbook will help you to achieve this. If you want, you can combine the formulae in C and D into one.
7 Replies
gobertchristphe1968 You need to extract the last four digits of each number as a time, first. Perhaps the attached workbook will help you to achieve this. If you want, you can combine the formulae in C and D into one.
Riny_van_Eekelen : Hello Riny Than you very much for your reply. I do not understand your formula but it works. However should be possible to simply it if i can separe hours and minutes in a column ? I have try fi to have 17h(in a3) and 02m(in a4) but i can not apply formula =temps(a3;a4;0) why. Thans for your help. Regards
gobertchristphe1968 Well, the formula is doing the separating for you, but without creating separate cells for the hours and minutes. But you could use "Text-to-columns" on the Data ribbon and split the text string like "202107231702" into three columns.
It should then look like this:
Note that you should NOT have the "h" and the "m" in the respective cells for the hours and the minutes! Then the TEMPS formula will work as desired.
