date tracking -*

[K wilson]

I am stuck with a complicate task
In (F:2) I have a service requested information, BCK=3
days, REP= 7 days, OVH= 14 days from date of the Repair
order (G:2). In (H:2) is where the projected return date
should appear.

I also need to track days past due.
In (H:2) i have a projected return date, In (Q:2) I have
the return date, I (R:2) should show how many days past
due.

Is this possible? I have been using a book on excel
formulas, but have not been able to figure this one.

any input or direction is appreciated

 

[Frank Kabel]

Hi
try the following in H2:
=G2+IF(F2="BCK",3,IF(F2="REP",7,IF(F2="OVH",14,0)))
if you want to include weekends.
for a formula taking only workdays into account use the following in H2
=WORKDAY(G2,IF(F2="BCK",3,IF(F2="REP",7,IF(F2="OVH",14,0))))
in both cases format the cell as date

enter the following in R2:
=IF(Q2>H2,Q2-H2,"")
or for workdays:
=IF(NETWORKDAYS(H2,Q2)>0,NETWORKDAYS(H2,Q2),"")
format the cells as 'Number'

you may have to install the Analysis Toolpak Add-in for the workday
formulas (goto 'Tools - Add-In Manager" and check this Add-In)

 

[Scott]

Hi K wildon!
Choose another cell ex: A1 and enter the repair order date formatted as a date. In H2 enter =IF(F2="BCK",3,IF(F2="REP",7,IF(F2="OVH",14,"")))+A1 or =IF(F2="BCK",3,IF(F2="REP",7,IF(F2="OVH",14,0)))+A1 or =IF(F2="BCK",3,IF(F2="REP",14))+A1 depending on what you what it to be it there's nothing in F2.

For you # of days past due: in R2 put =Q2-H2 or =H2-Q2 depending on whether you want past due to be postive and before due negative or vice versa, and make sure it's not formatted as a date. General should work fine.

Scott

----- K wilson wrote: -----

I am stuck with a complicate task
In (F:2) I have a service requested information, BCK=3
days, REP= 7 days, OVH= 14 days from date of the Repair
order (G:2). In (H:2) is where the projected return date
should appear.

I also need to track days past due.
In (H:2) i have a projected return date, In (Q:2) I have
the return date, I (R:2) should show how many days past
due.

Is this possible? I have been using a book on excel
formulas, but have not been able to figure this one.

any input or direction is appreciated

 

[Scott]

Opps, didn't notice you already had G2 as repair order date. If you want to use my example just subtitute A1 for G2

Scott

 

[Ken]

Frank

Thank you for taking the time to help

Ken

-----Original Message-----
Hi
try the following in H2:
=G2+IF(F2="BCK",3,IF(F2="REP",7,IF(F2="OVH",14,0)))
if you want to include weekends.
for a formula taking only workdays into account use the following in H2
=WORKDAY(G2,IF(F2="BCK",3,IF(F2="REP",7,IF (F2="OVH",14,0))))
in both cases format the cell as date

enter the following in R2:
=IF(Q2>H2,Q2-H2,"")
or for workdays:
=IF(NETWORKDAYS(H2,Q2)>0,NETWORKDAYS(H2,Q2),"")
format the cells as 'Number'

you may have to install the Analysis Toolpak Add-in for the workday
formulas (goto 'Tools - Add-In Manager" and check this Add-In)


--
Regards
Frank Kabel
Frankfurt, Germany



.