Excel 2007 : Refine a countif formula

**joesport5** · 04-23-2010, 12:03 PM

I am working on trying to get time down to seconds by using a DTG.

Column A has inputs of: 17100932APR10 (17 the day 10:09:32 the Time)

Column AN has the same input but different min and sec.

What I want to capture is just the difference in the min and sec.

this formula below works for the hours and min, I cannot seem to get it the way I want it.

=IF(COUNTA(A15:AN15),REPLACE(AN15,3,5,"")+TEXT(MID(AN15,3,4),"00\:00")-REPLACE(A15,3,5,"")-TEXT(MID(A15,3,4),"00\:00"),"")

**sailepaty** · 04-23-2010, 01:47 PM

Maybe,

=TIMEVALUE(MID(AN15,3,2)&":"&MID(AN15,5,2)&":"&MID(AN15,7,2))-TIMEVALUE(MID(A15,3,2)&":"&MID(A15,5,2)&":"&MID(A15,7,2))

Regards

**daddylonglegs** · 04-23-2010, 01:47 PM

Try using this formula

=IF(COUNTA(A15:AN15),TEXT(MID(AN15,3,6),"00\:00\:00")-TEXT(MID(A15,3,6),"00\:00\:00"),"")

format result cell as mm:ss

Assumes that that A15 and AN15 entries will always be on the same day.....

**daddylonglegs** · 04-23-2010, 02:53 PM

......I note that in your original formula you are using this part

REPLACE(AN15,3,5,"")

....to get the date without the time, but as the time is 6 digits in your example (and I assume there's always a leading zero when hours are 9 or less) that should be a 6 not a 5...so if you want to take a date change into account (as your original formula does) you could do that like this

=IF(COUNTA(A15:AN15),REPLACE(AN15,3,6,"")+TEXT(MID(AN15,3,6),"00\:00\:00")-REPLACE(A15,3,6,"")-TEXT(MID(A15,3,6),"00\:00\:00"),"")

or if the date change will only ever be to the next day you could do this

=IF(COUNTA(A15:AN15),TEXT(MID(AN15,3,6),"00\:00\:00")-TEXT(MID(A15,3,6),"00\:00\:00")+(LEFT(A15,2)<>LEFT(AN15,2)),"")