Hope someone can help..2 issues here when my data arrives in Excell I need it to be in HH:MM for time calculations..correct? however my data appears XXXX example 647 instead of 06:47. how can I do this. 2nd point is i need to calculate how many minutes between say 23:15 and 06:47. Again help please

To convert HHMM to HH:MM or HMM to H:MM try this formula: Assuming HHMM or HMM in A1: =LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2) To determine the number of minutes between 2 time values, try this: With your times in B1 and B2, put this in C1: =IF(B1>B2,B2-B1,B1-B2) Excel can't handle negative times, so you've got to subtract the smaller time from the larger time. (This may cause issues if you cross the midnight threshold.) This formula will result in an answer that has hours and minutes, e.g. 16:47. To convert this to minutes, use: =HOUR(C1)*60+MINUTE(C1)

You may get problems if you're pulling in times like 0003 with the above formula. Use the following instead (assumes the time you get from your system is in cell A1: =TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(TEXT(A1,"0000"),2),0) Then when you're subtracting the times, I'd use the following formula (assumes the "correct" times are in B1 and B2), and B1 is the "start", B2 the "end": =IF(B1>B2,1+B2-B1,B2-B1)*24*60

Good catch - sort of. :-) With my formula, 3 would be an issue, but 0003 - as long as that is what appears in the cell - should not be a problem. In any case, your suggestion should work for all cases, so that's the better one to use.