# Formula to copy time data and format it in a specific way depending om

#### Event2020

##### Board Regular
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 cannot get the formula to only use the first “:” as a delimiter and then look at the two numbers before it.

Many thanks

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Fluff

##### MrExcel MVP, Moderator
Cross posted Formula to Format how time (hh:mm:ss) is displayed based on specific criteria?

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Replies
3
Views
153
Replies
17
Views
451
Replies
4
Views
130
Replies
4
Views
99
Replies
3
Views
331

1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back