How can I take times from text and add to columns
- Thread starter Kev7607
- Start date
Hi Hui,
This is the format of the text in A2. I wish to take the values from Net Outage at the end and put it in three separate columns B2 - Days, C2 - Hours and D2 - Mins
xxx reported link speed unworkable. Link balanced and found to be correct. xxx agreed that ticket could be closed and issue outside JOM. ROOT CAUSE : Issue proved outside JOM Stop Clocks: StopClock01: 171725z Apr 18 to 190935z Apr 18 - Cust investigating StopClock02: 190935z Apr 18 to 231120z Apr 18 - Waiting for Cust to call SC Total Outage - 5days 18hours 27mins Clock Stops - 5days 17hours 55mins Net Outage - 0days 0hours 32mins
Regards
This is the format of the text in A2. I wish to take the values from Net Outage at the end and put it in three separate columns B2 - Days, C2 - Hours and D2 - Mins
xxx reported link speed unworkable. Link balanced and found to be correct. xxx agreed that ticket could be closed and issue outside JOM. ROOT CAUSE : Issue proved outside JOM Stop Clocks: StopClock01: 171725z Apr 18 to 190935z Apr 18 - Cust investigating StopClock02: 190935z Apr 18 to 231120z Apr 18 - Waiting for Cust to call SC Total Outage - 5days 18hours 27mins Clock Stops - 5days 17hours 55mins Net Outage - 0days 0hours 32mins
Regards
Peter Bartholomew
Well-Known Member
Is there something here that captures your requirement?
Peter Bartholomew
Well-Known Member
I need "net outage" as the start rather than "- net outage -"
Hi Pete.
Thanks for the reply.
That looks like what Im trying to do. Can the formula be put into the days, hours, minutes cell(s) or does there need to be a separate calculation box as per your example ?
This is row one of 50 plus similar faults
Last edited:
Peter Bartholomew
Well-Known Member
Is this closer? I have used a table to provide meaningful descriptions of the values. If you have any need for aggregated stats, I would recommend expressing the outages as decimals in units of a day.
Peter Bartholomew
Well-Known Member
Peter Bartholomew
Well-Known Member
Kev
This is a working version of your spreadsheet but I am not sure what you will make of it because our ways of working on Excel are rather different. You have columns containing approximate parts of the time interval that I would simply delete. They are not needed to calculate the duration of an interval; that can be achieved by subtracting the two date/times. I will leave it to Hui to decide what advice you should take from this forum.
This is a working version of your spreadsheet but I am not sure what you will make of it because our ways of working on Excel are rather different. You have columns containing approximate parts of the time interval that I would simply delete. They are not needed to calculate the duration of an interval; that can be achieved by subtracting the two date/times. I will leave it to Hui to decide what advice you should take from this forum.
Peter Bartholomew
Well-Known Member
I am out of the office this morning but if you are interested, I will write some guidance later today.
The immediate question that comes to mind if you are copying formulas to a different workbook is have you defined the names 'comments', 'detected', 'restored', 'start', 'dd', 'mm' and 'hh'? This is done in Name Manager. The references are all mixed in that they apply to a particular column (the letter is prefixed by a '$' as in =Sheet1!$F3 which defines the name 'comments') but the row is relative (in this case it is the row in which the formula is evaluated).
The immediate question that comes to mind if you are copying formulas to a different workbook is have you defined the names 'comments', 'detected', 'restored', 'start', 'dd', 'mm' and 'hh'? This is done in Name Manager. The references are all mixed in that they apply to a particular column (the letter is prefixed by a '$' as in =Sheet1!$F3 which defines the name 'comments') but the row is relative (in this case it is the row in which the formula is evaluated).
NARAYANK991
Excel Ninja
Hi ,
Creating named ranges which do not use absolute referencing is always a risky move , since the reference will depend on where the cursor is when the name is created.
To take the same example given in Peter's post , if you have to create a name comments , and refer it to =Sheet1!$F3 , you have to ensure that the cursor is in some cell in row 3.
If the cursor is in any other row when you define the named range , the reference will go wrong.
The same applies when you create a named range where the column reference is not absolute.
Narayan
Creating named ranges which do not use absolute referencing is always a risky move , since the reference will depend on where the cursor is when the name is created.
To take the same example given in Peter's post , if you have to create a name comments , and refer it to =Sheet1!$F3 , you have to ensure that the cursor is in some cell in row 3.
If the cursor is in any other row when you define the named range , the reference will go wrong.
The same applies when you create a named range where the column reference is not absolute.
Narayan
Peter Bartholomew
Well-Known Member
Thanks Narayan.
Kev. Are you still getting 0? The other name that you need to check is 'start'. If you enter '=start' into a cell to the right of your table such as V3 you should see the character count 346 with 214 on the next row.
To set the name up you go into Name Manager, select 'start' from the list and enter
=SEARCH("Net Outage", comments)
into the 'Refers to:' dialogue box. If you need to be taken through the process in greater detail then let me know.
Kev. Are you still getting 0? The other name that you need to check is 'start'. If you enter '=start' into a cell to the right of your table such as V3 you should see the character count 346 with 214 on the next row.
To set the name up you go into Name Manager, select 'start' from the list and enter
=SEARCH("Net Outage", comments)
into the 'Refers to:' dialogue box. If you need to be taken through the process in greater detail then let me know.