date tracking -*

  • Thread starter Thread starter K wilson
  • Start date Start date
K

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
 
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)
 
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
 
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
 
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



.
Click to expand...
 
Back
Top