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

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
88
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,805
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top