to calculate status

[zoharb]

query@23jan25.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Jesus/CL ID	Date	Description	Po#	File Count	Order Placed By	Order Type	Stitches	Amount	Status
2	643	01/20/2025 12:21	Cornerstone Group		PXF	Wendy Hayes	digitizing	2098	4.18
3	370	01/20/2025 20:46	Alhambra Complete Building Plans.	4701		Ray Bailey	ARTWORK	USA		outside
4	631	01/20/2025 17:41	Lady Silk		AUS	Sally Munro	digitizing	7441	14.81
5	631	01/20/2025 17:41	Lady Silk	1	AUS	Sally Munro	digitizing	9916	19.73
6	6	01/21/2025 02:46	Master Flooring		PXF	Chris	Digitizing	6283	12.5
7	671	01/21/2025 04:07	Art for Turner Tavern		Quote 14.95	Patty Littleton	artwork		14.95	approved
8	605	01/21/2025 04:29	Sterrato			Shashi	artwork		5.95
9	631	01/21/2025 05:04	Sunstone	1	AUS	Sally Munro	digitizing	2368	4.71
10	127	01/21/2025 06:22	CE GROUP		PXF	Heather	digitizing	5088	10.13
11	6	01/21/2025 09:50	Alfagen			Chris	Artwork		5.95
12	395	01/21/2025 22:22	Apex		KM	Kimberly McKeever	Digitizing		0	pending-42.52hr
13	44	01/21/2025 22:30	WURTH WORKOUT WEEK	RUSH		Joshua	Artwork		8.95
14	225	01/22/2025 01:47	Qualified Tent Banner	Quote19.95		JUSTIN	artwork			pending39.11hr
15	6	01/22/2025 04:06	TMD Field Maintenance Logo		PXF	Linda	Digitizing		0	pending-36.79hr
16	83	01/22/2025 05:46	BAAA EPS			leome	artwork		5.95
17	83	01/22/2025 05:53	Bocce			leome	artwork		5.95
18	71	01/22/2025 07:04	Hearts of hope			Nicki Hopkins	artwork		5.95
19	6	01/22/2025 07:39	Tweed Escapes Logo.		PXF	Chris	Digitizing	4248	8.45
20	8	01/22/2025 07:39	Port Phillip Timber floor		2 options	Tracy	artwork	send also to samgmail	39.95	approved
21	6	01/22/2025 10:33	Physio Place		PXF	Chris	Digitizing	3456	6.88
22	83	01/22/2025 10:30	H A Reed Locksmith		Leoma -pxf	leome	digitizing		0	pending#30.39hr
23	83	01/22/2025 10:33	Inspiring		Leoma -pxf	leome	digitizing	6309	12.55
24	194	01/22/2025 10:59	Logo prostate		PXF	Michelle	Digitizing	3734	7.43
25	58	01/22/2025 11:43	Franks Gardening			Lisa allan	artwork		5.95
26	668	01/22/2025 23:09	CUT line	RUSH	2	Angela Campbell	artwork		5.95
27	668	01/23/2025 00:08	Station 18	RUSH	Quote 20.95	Angela Campbell	artwork			awaiting approval
28	225	01/23/2025 02:15	Steele	9202	Quote5.95	JUSTIN	artwork		5.95	approved
29	21	01/23/2025 03:01	Pinnies logo		Quote11.9	Tim Macy	artwork			awaiting approval
30	225	01/23/2025 03:48	Century 21		Quote5.95	JUSTIN	artwork			awaiting approval
31	671	01/23/2025 06:39	Art for Turner Tavern	new @23jan25		Patty Littleton	artwork		5.95
32	58	01/23/2025 08:26	GR designs			Lisa allan	artwork		5.95
33	6	01/23/2025 06:01	Waterloo			Chris	artwork		8.95
34	6	01/23/2025 05:31	Brunswick Heads	CAP	PXF	Chris	Digitizing	2925	5.82
35	6	01/23/2025 05:31	Brunswick Heads		PXF	Chris	Digitizing	10161	20.22
36	127	01/23/2025 11:51	RD-0237	Bec's shop	PXF	Heather	digitizing	4060	8.08
37	127	01/23/2025 11:56	TKS logo		PXF	Heather	digitizing		0	pending-4.96hr
38	127	01/23/2025 11:56	TKS logo			Heather	artwork			pending4.96hr
Sheet1

Cell Formulas
Range	Formula
I2,I34:I37,I21,I24,I19,I15,I12,I9:I10,I4:I6	I2	=IF(H2<32665,(ROUND(H2*(1.99/1000),2)),65)
J2,J32:J38,J21,J24:J25,J14:J19,J9:J12,J4:J6	J2	=IF(AND(G2="digitizing",H2=""),"pending-"&ROUND((NOW()-B2)*24,2)&"hr",(IF(AND(G2="artwork",I2=""),"pending"&ROUND((NOW()-B2)*24,2)&"hr","")))
I22:I23	I22	=ROUND(H22*(1.99/1000),2)
J22:J23,J26,J31	J22	=IF(AND(G22="digitizing",H22=""),"pending#"&ROUND((NOW()-B22)*24,2)&"hr",(IF(AND(G22="artwork",I22=""),"pending #"&ROUND((NOW()-B22)*24,2)&"hr","")))

Has written formula is Column J for pending,
but now also wants to include "Quote required" if "Quote" IS written in any case in Column D or E
and
if has $ sign or number(upto 3 digit or upto 3 digits with decimal) after that(quote) it should be "awaiting approval"
formula is needed in Column J

 

[Special-K99]

You can shorten that EXISTING J2 to this
=IF(OR(AND(G2="digitizing",H2=""),AND(G2="artwork",I2="")),"pending"&ROUND((NOW()-B2)*24,2)&"hr","")

You also need to explain what takes precedent with the status, "pending" or "Quote required" or "awaiting approval"

 

[zoharb]

When client gives any working without asking Quote it is simply pending(the thing that I have already done). till its not over i column is not populated , so pending status shows(in Column J)
But some clients asks for Quote. so we tag it with "Quote". then status should be "Quote required"(in column J), but after we gave "Quote" . the status should change to "awaiting approval"
Now when the client approves Quote-it is mentioned as "approved" in Column D, E or H(whichever is empty and at that status in column J will change to "pending" and as usual , formula of pending as mentioned previously should trigger .

Hope I am clear.
If not, so please mention where in logic I have made mistake and I will be oblidged to you to give proper answer.

Zohar batterywala