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

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

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

--
HTH

Bob Phillips

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

"Dipwind" wrote in
message ...

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

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


--
Dipwind
------------------------------------------------------------------------
Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276
View this thread: http://www.excelforum.com/showthread...hreadid=568990

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 HE

http://office.microsoft.com/en-us/as...609811033.aspx


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

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,"Y M")&" 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

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


--
Dipwind
------------------------------------------------------------------------
Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276
View this thread: http://www.excelforum.com/showthread...hreadid=568990

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

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


--
Dipwind
------------------------------------------------------------------------
Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276
View this thread: http://www.excelforum.com/showthread...hreadid=568990