What function?

[Dipwind]

Hi,

I have a problem, and alone I simply can't get a solution, because I
don't Know how.


Can somebody help, please?

I leave an attached file.


+-------------------------------------------------------------------+
|Filename: problem.bmp |
|Download: http://www.excelforum.com/attachment.php?postid=5155 |
+-------------------------------------------------------------------+

 

[Pete_UK]

Many posters will not open attached files, and some newsgroups don't
support them - can you try to describe your problem?

Pete

 

[Bob Phillips]

Try conditional formatting, http://www.contextures.com/xlCondFormat01.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Franz Verga]

Hi,

I have a problem, and alone I simply can't get a solution, because I
don't Know how.

First of all I'll suggest you to use the function TODAY(), instead of NOW(),
because NOW has also the time inside an not only the date.

To make the cells red, you can use the conditonal formatting (First of all
select the cells you want to format, then menu Format, Conditional
Formatting, choose "formula is" and type: =$D4<>"", then click on Format and
choose the colour red for background).

To stop counting years, you can use an IF function in G4; to have the
counting in years, months and days, you can use the DATEDIF function, so the
formula in G4 should be:

=IF(D4<>"",DATEDIF(C4,$B$1,"Y")&" Y "&DATEDIF(C4,$B$1,"YM")&" M
"&DATEDIF(C4,$B$1,"MD")&" D",DATEDIF(C4,D4,"Y")&" Y "&DATEDIF(C4,D4,"YM")&"
M "&DATEDIF(C4,D4,"MD")&" D")

to have references about datedif FUNCTION YOU CAN CHECK HERE:

http://office.microsoft.com/en-us/assistance/HA011609811033.aspx


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dipwind]

I sent the file attach because my English is not good.

but ok, I will try.

I have a sheet that counts how long each worker is in the company.
a cell "B2" is formatted in date with the function =Now().
another cell "C4" has the admission date, each worker's time is counted
in "E4" with =(B2-C4)/365.
B2=07-Aug-2007
C4=02-Jul-1990
this gives the result of 16 years and eleven parts of 365 days.
I intend to obtain this result in Y/M/D.
the sheet has in the same row other cell "D4" with the ending date.
this cell is always blank until an employee finish with us.
I would like that cell when filled out the time of work "(B2-C4)/365"
stopped counting, and at the same time all of the cells of the same Row
turn red, except the cell E4.

Thanks Pete
João

 

[Franz Verga]

First of all I'll suggest you to use the function TODAY(), instead of
NOW(), because NOW has also the time inside an not only the date.

To make the cells red, you can use the conditonal formatting (First
of all select the cells you want to format, then menu Format,
Conditional Formatting, choose "formula is" and type: =$D4<>"", then
click on Format and choose the colour red for background).

To stop counting years, you can use an IF function in G4; to have the
counting in years, months and days, you can use the DATEDIF function,
so the formula in G4 should be:

=IF(D4<>"",DATEDIF(C4,$B$1,"Y")&" Y "&DATEDIF(C4,$B$1,"YM")&" M
"&DATEDIF(C4,$B$1,"MD")&" D",DATEDIF(C4,D4,"Y")&" Y
"&DATEDIF(C4,D4,"YM")&" M "&DATEDIF(C4,D4,"MD")&" D")

sorry, the above formula should be:

=IF(D4<>"",DATEDIF(C4,D4,"Y")&" Y "&DATEDIF(C4,D4,"YM")&"
M "&DATEDIF(C4,D4,"MD")&" D",DATEDIF(C4,$B$1,"Y")&" Y
"&DATEDIF(C4,$B$1,"YM")&" M
"&DATEDIF(C4,$B$1,"MD")&" D")

but a better solution could be:

=IF(D4<>"",DATEDIF(C4,D4,"y")&" y
"&IF(DATEDIF(C4,D4,"YM")=0,"",DATEDIF(C4,$B$1,"YM")&"
m")&IF(DATEDIF(C4,D4,"md")=0,"",DATEDIF(C4,D4,"MD")&"
D"),DATEDIF(C4,$B$1,"y")&" y
"&IF(DATEDIF(C4,$B$1,"YM")=0,"",DATEDIF(C4,$B$1,"YM")&" m
")&IF(DATEDIF(C4,$B$1,"MD")=0,"",DATEDIF(C4,$B$1,"MD")&" D"))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dipwind]

Franz, everything works well, except, when I apply the solution that you
wrote. with DATEDIF.
I receive a message saying the formula you typed contains an error.

I already reviewed the formula and it is as you wrote.

can you help me?

João

 

[Franz Verga]

Franz, everything works well, except, when I apply the solution that
you wrote. with DATEDIF.
I receive a message saying the formula you typed contains an error.

I already reviewed the formula and it is as you wrote.

can you help me?

João

I'm not sure, but maybe to use DATEDIF you need to install the Analisys
ToolPak (menu Tools, Add-in, check Analisys Toolpak). Or it dependes on the
language you are working in... In Italian the function is DATA.DIFF...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dipwind]

Franz,

The formula works in the perfection, it counts the days and it stops
counting when the ending dates is written.

But in the cell years of service when D4 is filled out returns #NAME!.


what should I make for the result be the difference between D4-C4?

many thanks
João

 

[Franz Verga]

Franz,

The formula works in the perfection, it counts the days and it stops
counting when the ending dates is written.

But in the cell years of service when D4 is filled out returns #NAME!.


what should I make for the result be the difference between D4-C4?

many thanks
João

I don't know why a such behaviour... Maybe you could upload your file to
www.savefile.com so I can see what is wong, because the formula works fine
for me...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dipwind]

Franz,

http://www.savefile.com/files/1345175

 

[Franz Verga]

Franz,

http://www.savefile.com/files/1345175

Hi João,

there were two extra space inside the first DATEDIF function in the second
part of IF, maybe due to copy & past...
So the right formula should be:

=IF(D5<>"",DATEDIF(C5,D5,"y")&"y"&IF(DATEDIF(C5,D5,"YM")=0,"",DATEDIF(C5,$B$2,"YM"
)&"m")&IF(DATEDIF(C5,D5,"md")=0,"",DATEDIF(C5,D5,"MD" )&"D"),DATEDIF(C5,$B$2,"y")&"y"&IF(DATEDIF(C5,$B$2,"YM")=0,"",DATEDIF(C5,$B$2,"YM")&"m")&IF(DATEDIF(C5,$B$2,"MD")=0,"",DATEDIF(C5,$B$2,"MD")&"d"))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dipwind]

Well, Works in a perfection.

Thank's very much

Joã

 

[Dipwind]

I still have a problem, the cell H5 has this formula.
..=IF(E5=20,COUNTIF(G5,">=10y")+COUNTIF(G5,">=12y")+COUNTIF(G5,">=14y")+COUNTIF(G5,">=16y")+COUNTIF(G5,">=18y")).

every time that G5 has a value >= "10y", H5 should add 1.

but when I fill out C5, with an answer value in G5 of + 18y, H5 = 5,
everything well until here.

writing 02/08/1988 in C5 the result in H5 is 5, but when I alter the
value of C5 for 02/08/1999, H5 should be "0" but it continues with the
value 5.

what should I make? or what I did wrong

thanks

http://www.savefile.com/files/1345175

 

[Franz Verga]

I still have a problem, the cell H5 has this formula.
=IF(E5=20,COUNTIF(G5,">=10y")+COUNTIF(G5,">=12y")+COUNTIF(G5,">=14y")+COUNTIF(G5,">=16y")+COUNTIF(G5,">=18y")).

every time that G5 has a value >= "10y", H5 should add 1.

but when I fill out C5, with an answer value in G5 of + 18y, H5 = 5,
everything well until here.

writing 02/08/1988 in C5 the result in H5 is 5, but when I alter the
value of C5 for 02/08/1999, H5 should be "0" but it continues with the
value 5.

what should I make? or what I did wrong

Just change the formula in H5 with this:

=IF(E5=20,(VALUE(LEFT(G5,FIND("y",G5)-1))>=10)+(VALUE(LEFT(G5,FIND("y",G5)-1))>=12)+(VALUE(LEFT(G5,FIND("y",G5)-1))>=14)+(VALUE(LEFT(G5,FIND("y",G5)-1))>=18))




--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Dipwind]

Franz,

You saved me again.

I only had to increase the bit to count the 16 in the formula that yo
sent to me.

this project is ended and I want to thank you again.

Many Thanks
Joã

 

[Franz Verga]

Franz,

You saved me again.

I only had to increase the bit to count the 16 in the formula that you
sent to me.

this project is ended and I want to thank you again.

Many Thanks
João

You're welcome.


--
Glad I helped you.

Ciao

Franz Verga from Italy