Jul 23 2021 09:18 AM
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
Jul 23 2021 02:00 PM
Solution@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.
Jul 23 2021 10:16 PM
@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
Jul 23 2021 10:51 PM
@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.
Jul 23 2021 10:55 PM
Jul 23 2021 11:07 PM
@gobertchristphe1968 Perhaps you can upload your file, or at least the relevant part of it.
Jul 23 2021 11:12 PM
Jul 28 2021 11:02 AM
@Riny_van_Eekelen Thanks again. The formula works. Thanks for your help.
Jul 23 2021 02:00 PM
Solution@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.