Hello everyone,
I've been struggling to find an appropriate formula I can use to autofill or to use IF / VLOOKUP for specific data text based on what data I have in column A.
I'm not sure if this can be done in excel in anyway, I've been spending a few hours searching for a hint or an idea on google, but I couldn't find anything that I understand.
To get straight to the point:
I would like to autofill the B column with one of the following values:
Breached
Less than 5 hours
Less than 1 Day
More than 1 Day
5 Days
More than 5 Days
For example, if in column A I have "0 Seconds", in column B I would like to have "Breached".
If in column A I have anything greater than "1 Seconds" until "4 hours 59 Seconds"= "Less than 5 hours"
If in column A I have anything between "5 hours 0 Minutes" and "23 Hours 59 Minutes"= "Less than 1 Day"
If in column A I have anything between "24 Hours 1 Minute" and "4 Days 23 Hours 59 Minutes"= "More than 1 Day"
If in column A I have "5 Days 00 Hours 0 Minutes"= "5 Days"
If in column A I have anything greater than "5 Days 0 Hours 1 Minute" = "More than 5 Days"
The issue is, the values from column A are not static, they will change on a daily basis and I cannot transform them into actual Date&Time in excel, because I extract them from a ticketing system and I extract them right in this format "xx Days xx Hours xx Minutes" or "xx Seconds".
I'm not sure how I can do this with a formula instead of doing it manually every day...
It's difficult to do it manually since I have around 400-600 entries.
Does anyone have any idea?
Bookmarks