Formula to Format how time (hh:mm:ss) is displayed based on specific criteria?

I am trying to write a formula that pulls data from cell A1 on Sheet 2 into cell B1 on Sheet 1

The data in A1 is in hh:mm:ss format.

Using Cell A1 with a value of 00:06:20 as a working example
I would like the formula to do one of two things, starting at A2 for every cell in Column A on Sheet 2 depending upon if a cell has an entry that is either hour, minutes & seconds (01:06:20) or just minutes and seconds (00:06:20).

If a cell has an entry like 01:06:20 then the formula should return 01:06:20 “hh:mm:ss”

If a cell has an entry like 00:06:20 then the formula should return 00:06:20 “mm:ss”

I have tried to have the formula use the first “:” character as a delimiter and then check if the two characters before it “00:” are any other character then “0”. (meaning the value is 0 hours 06 minutes and 20 seconds) and then return the appropriate format as described below.

If true then format the cell as general and insert “06:20”

If false then format the cell as general and insert “01:06:20”

Then move to next cell in range “A2:A) and repeat until the last cell

Of course the time value in each cell A1 on Sheet 2 will be different but the constant character will be the FIRST “:” and that any number before it apart from “0” will mean it is an hh:mm:ss value.

I have been at this all day and I cannot get the formula to only use the first “:” as a delimiter and then look at the two numbers before it.

Many thanks

Not sure quite what you want to do, but you can determine if there are any hours like
=IF(A2<1/24,"mm:ss","hh:mm:ss")

Originally Posted by Fluff13

Not sure quite what you want to do, but you can determine if there are any hours like
=IF(A2<1/24,"mm:ss","hh:mm:ss")

Thank you Fluff13 - this has given me a start.

Originally Posted by protonLeah

Select column A and apply custom format formula:

Please Login or Register  to view this content.

Thank you, I will give it a try.

My apologies to all for cross posting, it will not happen again.

Also asked here https://www.mrexcel.com/board/thread...ng-om.1137860/ but received no replies.

Hi FDibbins

I did that right after my last post here, apologies again.

Thank you Fluff13 - this has given me a start.

You're welcome & thanks for the feedback.