Time and If Functions / Conditional Formatting

[Harbor1704]

I have a report that I pull showing the time that files are completed. I am trying to do an IF function where if a time is over a certain amount it would show how many days the file is overdue and then if it is on time or late. I am having no luck figuring out the formula and I think it is partly because the report does not format the production time. It should show like below, I have also included the ranges and a mini sheet sampling to show what I am working with:

There are specific Ranges for each.

1 Day = 0 to 24 Hours (0 Mins to 1 Day) On Time

2 Days = 24+ Hrs to 52 Hrs (1 day 1min to 2 days 4 hours) On Time

3 – 4 Days = 52+ Hrs to 96 Hrs. (2 days 4 hours 1 min to 4 days) Late

Over 4 Days = 96+ Hrs (4 days 1 min. +) Late

AGENT TURN AROUND.xls
	F	G	H	I
1	Production Time	Month	Range	On Time/Late
2	4 hour(s) 6 min.	Aug
3	3 hour(s) 7 min.	Aug
4	2 hour(s) 58 min.	Aug
5	1 day(s) 2 hour(s) 18 min.	Aug
6	6 hour(s) 6 min.	Aug
7	1 hour(s)	Aug
8	2 hour(s) 41 min.	Aug
9	1 day(s) 2 hour(s) 2 min.	Aug
10	1 hour(s) 5 min.	Aug
11	2 hour(s) 6 min.	Aug
12	1 hour(s) 19 min.	Aug
13	2 hour(s) 14 min.	Aug
14	6 day(s) 2 hour(s) 20 min.	Aug
15	3 hour(s) 37 min.	Aug
16	1 hour(s) 13 min.	Aug
17	1 hour(s) 20 min.	Aug
18	1 day(s) 5 hour(s) 58 min.	Aug
19	1 day(s) 1 hour(s) 32 min.	Aug
20	2 hour(s) 52 min.	Aug
21	1 hour(s) 53 min.	Aug
22	1 hour(s) 31 min.	Aug
23	3 hour(s) 26 min.	Aug
24	1 day(s) 6 hour(s) 8 min.	Aug
Sheet1

 

[vladimiratanasiu]

I have a report that I pull showing the time that files are completed. I am trying to do an IF function where if a time is over a certain amount it would show how many days the file is overdue and then if it is on time or late. I am having no luck figuring out the formula and I think it is partly because the report does not format the production time. It should show like below, I have also included the ranges and a mini sheet sampling to show what I am working with:

View attachment 71457


There are specific Ranges for each.

1 Day = 0 to 24 Hours (0 Mins to 1 Day) On Time

2 Days = 24+ Hrs to 52 Hrs (1 day 1min to 2 days 4 hours) On Time

3 – 4 Days = 52+ Hrs to 96 Hrs. (2 days 4 hours 1 min to 4 days) Late

Over 4 Days = 96+ Hrs (4 days 1 min. +) Late

AGENT TURN AROUND.xls
	F	G	H	I
1	Production Time	Month	Range	On Time/Late
2	4 hour(s) 6 min.	Aug
3	3 hour(s) 7 min.	Aug
4	2 hour(s) 58 min.	Aug
5	1 day(s) 2 hour(s) 18 min.	Aug
6	6 hour(s) 6 min.	Aug
7	1 hour(s)	Aug
8	2 hour(s) 41 min.	Aug
9	1 day(s) 2 hour(s) 2 min.	Aug
10	1 hour(s) 5 min.	Aug
11	2 hour(s) 6 min.	Aug
12	1 hour(s) 19 min.	Aug
13	2 hour(s) 14 min.	Aug
14	6 day(s) 2 hour(s) 20 min.	Aug
15	3 hour(s) 37 min.	Aug
16	1 hour(s) 13 min.	Aug
17	1 hour(s) 20 min.	Aug
18	1 day(s) 5 hour(s) 58 min.	Aug
19	1 day(s) 1 hour(s) 32 min.	Aug
20	2 hour(s) 52 min.	Aug
21	1 hour(s) 53 min.	Aug
22	1 hour(s) 31 min.	Aug
23	3 hour(s) 26 min.	Aug
24	1 day(s) 6 hour(s) 8 min.	Aug
Sheet1

I have a report that I pull showing the time that files are completed. I am trying to do an IF function where if a time is over a certain amount it would show how many days the file is overdue and then if it is on time or late. I am having no luck figuring out the formula and I think it is partly because the report does not format the production time. It should show like below, I have also included the ranges and a mini sheet sampling to show what I am working with:

View attachment 71457


There are specific Ranges for each.

1 Day = 0 to 24 Hours (0 Mins to 1 Day) On Time

2 Days = 24+ Hrs to 52 Hrs (1 day 1min to 2 days 4 hours) On Time

3 – 4 Days = 52+ Hrs to 96 Hrs. (2 days 4 hours 1 min to 4 days) Late

Over 4 Days = 96+ Hrs (4 days 1 min. +) Late

AGENT TURN AROUND.xls
	F	G	H	I
1	Production Time	Month	Range	On Time/Late
2	4 hour(s) 6 min.	Aug
3	3 hour(s) 7 min.	Aug
4	2 hour(s) 58 min.	Aug
5	1 day(s) 2 hour(s) 18 min.	Aug
6	6 hour(s) 6 min.	Aug
7	1 hour(s)	Aug
8	2 hour(s) 41 min.	Aug
9	1 day(s) 2 hour(s) 2 min.	Aug
10	1 hour(s) 5 min.	Aug
11	2 hour(s) 6 min.	Aug
12	1 hour(s) 19 min.	Aug
13	2 hour(s) 14 min.	Aug
14	6 day(s) 2 hour(s) 20 min.	Aug
15	3 hour(s) 37 min.	Aug
16	1 hour(s) 13 min.	Aug
17	1 hour(s) 20 min.	Aug
18	1 day(s) 5 hour(s) 58 min.	Aug
19	1 day(s) 1 hour(s) 32 min.	Aug
20	2 hour(s) 52 min.	Aug
21	1 hour(s) 53 min.	Aug
22	1 hour(s) 31 min.	Aug
23	3 hour(s) 26 min.	Aug
24	1 day(s) 6 hour(s) 8 min.	Aug
Sheet1

I have a report that I pull showing the time that files are completed. I am trying to do an IF function where if a time is over a certain amount it would show how many days the file is overdue and then if it is on time or late. I am having no luck figuring out the formula and I think it is partly because the report does not format the production time. It should show like below, I have also included the ranges and a mini sheet sampling to show what I am working with:

View attachment 71457


There are specific Ranges for each.

1 Day = 0 to 24 Hours (0 Mins to 1 Day) On Time

2 Days = 24+ Hrs to 52 Hrs (1 day 1min to 2 days 4 hours) On Time

3 – 4 Days = 52+ Hrs to 96 Hrs. (2 days 4 hours 1 min to 4 days) Late

Over 4 Days = 96+ Hrs (4 days 1 min. +) Late

AGENT TURN AROUND.xls
	F	G	H	I
1	Production Time	Month	Range	On Time/Late
2	4 hour(s) 6 min.	Aug
3	3 hour(s) 7 min.	Aug
4	2 hour(s) 58 min.	Aug
5	1 day(s) 2 hour(s) 18 min.	Aug
6	6 hour(s) 6 min.	Aug
7	1 hour(s)	Aug
8	2 hour(s) 41 min.	Aug
9	1 day(s) 2 hour(s) 2 min.	Aug
10	1 hour(s) 5 min.	Aug
11	2 hour(s) 6 min.	Aug
12	1 hour(s) 19 min.	Aug
13	2 hour(s) 14 min.	Aug
14	6 day(s) 2 hour(s) 20 min.	Aug
15	3 hour(s) 37 min.	Aug
16	1 hour(s) 13 min.	Aug
17	1 hour(s) 20 min.	Aug
18	1 day(s) 5 hour(s) 58 min.	Aug
19	1 day(s) 1 hour(s) 32 min.	Aug
20	2 hour(s) 52 min.	Aug
21	1 hour(s) 53 min.	Aug
22	1 hour(s) 31 min.	Aug
23	3 hour(s) 26 min.	Aug
24	1 day(s) 6 hour(s) 8 min.	Aug
Sheet1

You have the in the next table a formula, to convert the string to time format

Book1
	A	B
1	Production Time
2	4 hours 6 minutes	0:04:06
3	3 hour(s) 7 min.	0:03:07
4	2 hour(s) 58 min.	0:02:58
5	1 day(s) 2 hour(s) 18 min.	1:02:18
6	6 hour(s) 6 min.	0:06:06
7	1 hour(s)	0:01:00
8	2 hour(s) 41 min.	0:02:41
9	1 day(s) 2 hour(s) 2 min.	1:02:02
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=SUMPRODUCT(VALUE(IFERROR(MID(0&A2,SEARCH({"d","h","m"},0&A2)-3,2),{0,0,0})),{3600,60,1})/86400

 

[Harbor1704]

Good day, I am still confused, can you please expand a bit more on how the formula fills in range (1 day, 2 days, etc) and on time/ late?

 

[vladimiratanasiu]

Good day, I am still confused, can you please expand a bit more on how the formula fills in range (1 day, 2 days, etc) and on time/ late?

You said having trouble because of production information being not as time formatted. As I mentioned, my formula helps you to extract from your text string number data and converts them in the necessary format, using them in other calculations.

 

[vladimiratanasiu]

Good day, I am still confused, can you please expand a bit more on how the formula fills in range (1 day, 2 days, etc) and on time/ late?

Is it what you want?

String date.xlsx
	H	I	J	K	L
1	Production Time		Month	Range	On time / Late
2	4 hours 6 minutes	4.10	July	1 Day	On time
3	3 hour(s) 7 min.	3.12	July	1 Day	On time
4	2 hour(s) 58 min.	2.13	July	1 Day	On time
5	1 day(s) 2 hour(s) 18 min.	26.13	July	2 Days	On time
6	6 hour(s) 6 min.	6.10	July	1 Day	On time
7	2 day(s) 4 hour(s) 18 min.	52.13	July	3-4 Days	Late
8	2 hour(s) 41 min.	2.02	July	1 Day	On time
9	5 day(s) 2 hour(s) 2 min.	122.03	July	Over 4 Days	Late
Sheet2

Cell Formulas
Range	Formula
K2:K9	K2	=IF(I2<24,"1 Day",IF(AND(I2>24,I2<52),"2 Days",IF(AND(I2>52,I2<96),"3-4 Days",IF(I2>96,"Over 4 Days"))))
L2:L9	L2	=IF(OR(K2="1 Day",K2="2 days"),"On time",IF(OR(K2="3-4 Days",K2="Over 4 days"), "Late"))
I2:I9	I2	=(DAY(SUM(MID(0&H2&"0000",FIND({"m","h","d"},0&H2&"xxmhd")-2,2)/{1440,24,1}))*24+HOUR(SUM(MID(0&H2&"0000",FIND({"m","h","d"},0&H2&"xxmhd")-2,2)/{1440,24,1}))+MINUTE(SUM(MID(0&H2&"0000",FIND({"m","h","d"},0&H2&"xxmhd")-2,2)/{1440,24,1}))/60)