What function?

**Dipwind** · 08-07-2006, 10:24 AM

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.

Pete_UK · 08-07-2006, 10:55 AM

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

Pete

Dipwind wrote:
> 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 |
> +-------------------------------------------------------------------+
>
> --
> Dipwind
> ------------------------------------------------------------------------
> Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276
> View this thread: http://www.excelforum.com/showthread...hreadid=568990

Bob Phillips · 08-07-2006, 11:10 AM

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

--
HTH

Bob Phillips

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

"Dipwind" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> Dipwind
> ------------------------------------------------------------------------
> Dipwind's Profile:
http://www.excelforum.com/member.php...o&userid=36276
> View this thread: http://www.excelforum.com/showthread...hreadid=568990
>

Franz Verga · 08-07-2006, 11:20 AM

Dipwind wrote:
> 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/as...609811033.aspx

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

**Dipwind** · 08-07-2006, 11:30 AM

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 · 08-07-2006, 11:50 AM

Franz Verga wrote:
> Dipwind wrote:
>> 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")
>

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** · 08-07-2006, 01:36 PM

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 · 08-07-2006, 01:50 PM

Dipwind wrote:
> 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** · 08-08-2006, 04:42 AM

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 · 08-08-2006, 07:15 AM

Dipwind wrote:
> 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** · 08-08-2006, 10:58 AM

Franz,

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

Franz Verga · 08-08-2006, 12:15 PM

Dipwind wrote:
> 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** · 08-08-2006, 12:37 PM

Well, Works in a perfection.

Thank's very much

João

**Dipwind** · 08-10-2006, 05:14 AM

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 · 08-10-2006, 09:50 AM

Dipwind wrote:
> 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** · 08-10-2006, 10:23 AM

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

Franz Verga · 08-10-2006, 10:45 AM

Dipwind wrote:
> 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