SOLVED

excel : Calculation problem between two sets of digits (hours and minutes)

Copper Contributor

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

7 Replies
best response confirmed by allyreckerman (Microsoft)
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.

@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:

Screenshot 2021-07-24 at 07.47.12.png

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.

 

 

I have done this but the TEMPS formula accepts h 17(i) but not the minutes 2 (j)

@gobertchristphe1968 Perhaps you can upload your file, or at least the relevant part of it.

the real file is at the office. i will tried again on Monday and contact you after. Thanks have a nice we

@Riny_van_Eekelen  Thanks again.  The formula works.  Thanks for your help.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
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.

View solution in original post