I need a help for the Excel formula.

[srikant_shetty]

Hi,

Here I need a help from you guys, I planned to create a sheet in that
example: C1 = Received file, E1=completion of maintenance, F1=Current
date and G1=Remarks.

If the E1=20-12-2005 and the F1=Today() and if F1 i.e. Current date is
15 days more thatn the E1 i.e. Completion date then it should display
"LATE" and if it is not less that 15 days then it should display blank
and also there are 2 condition that, if in the E1 column nothing date
is there then it should not show any no. of days. So please if anybody
can help me.

And also if in the C1 column is fed then it should not show LATE in the
remarks column.

Thanks
Sri

 

[Guest]

Sri,
I didn't fully understand your conditions but I think you need
something like this:

in G1: =IF(OR(E1="",C1="")," ",IF(F1-E1>15,"LATE",""))

(if E1 or C1 are blank, then G1 is blank, otherwise is LATE if difference of
dates is > 15)

Or if by 'C1 column is fed' you mean the literal "fed" then

=IF(OR(E1="",C1="fed")," ",IF(F1-E1>15,"LATE",""))

Hope this helps.

 

[srikant_shetty]

Thanks for your suggestions, but it is not fully completed. Th
following are my requirements.
I have planned to make a status report in my office.
In the C1=Received Files (as per the date) e.g.01-01-2006.
E1=Completion Date e.g. 16-01-2006
F1=Current date (TODAY())

My requirements are as follows:
1. If F1-E1>15 it should display in G1="LATE" only if C1 is blank, i
they have received the file in C1 and they fed the date then it shoul
not diplay in G1="LATE" instead it should be blank only.
2. If E1 is not fed any dates i.e. it is not completed then once agai
in G1 should be blank only.

I think now you understood my problems.

Thanks,
Sr

 

[Guest]

Sri,

=IF(E1="","",IF(AND(C1="",F1-E1>15),"LATE",""))

 

[srikant_shetty]

Hi,
Still I am having the problem I am not getting the correct m
requirements. Now as per your last reply, in my G1 column it is no
showing LATE display at all.
Once again I will repeat my requiements:
I am using 9 columns in a sheet as below:
A1 : Area code
B1 : Blank PPM Sheet to Section
C1 : Received filled PPM from Section
D1 : To section after feeding
E1 : Received corrected PPM from sction
F1 : Maintenance completed date
G1 : Reamrks
H1 : Current Date

What I want is,

1) If we have not sent the PPM sheet to any section i.e. when B1 i
blank, then all column should be blank only.

2) If when B1 is fed (i.e. date) and the F1 is fed after completion an
while comparing the H1(current date) with F1(completion date) i.e H1-F

15 days and if C1 is blank then in the G1 it should display "LATE".

3) If when B1 is fed and F1 is fed and C1 is also fed then G1 should b
blank only because as per our condition that we have received the PP
sheets before the 15 days after the completion date in F1 column.

So please help

Thanks,
Sr

 

[Guest]

Sri,
You keep changing your requirements! In your first post, F1
contained TODAY ... now you say H1 contains it. In addition, you talk about
testing B1 which (again) was not in your initial posting; that's why things
don't work!

In G1 =IF(AND(B1<>"",C1="",H1-F1>15),"Late","")

 

[Guest]

Added test for F1 not blank:

=IF(AND(B1<>"",C1="",F1<>"",H1-F1>15),"Late","")

 

[srikant_shetty]

Hi,

Thanks for your help and sorry for my late reply because I went for a
emergeny leave. Today I checked this thread and it is working fine.
Once again thanks for your help.

Thanks
Sri