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
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