Help with two items

[Ltat42a]

Hi...maybe you can help.
I made a spreadsheet to track monies spent on overtime. I have over 20 people that I'm tracking for. I have their name, number of hours, their hire date, and their hourly rate.

I need help with two items -
1. I have a section of the spreadsheet that summarizes everyone's name and the entire amount spent on OT. I'm using the formula =B2 to copy their name from cell B2 to L121. If there is no name listed in B2, L121 returns a value of 0. How can I show L121 as blank instead of 0? If a name is listed in B2, then it will appear in L121.

2. I'm using a date calculation to tell me how long they have been employed -
=DATEDIF(A7,TODAY(),"m")/12.
This gives me their time in service on the job. It gives me the calculation in a 2 decimal format. I need it in a whole number. When I change the format to a whole number, Excel rounds the number up if the value is more than .50 (i.e. 20.75 is rounded up to 21). How can I keep Excel from rounding the number up?
20.75 years should read as 20 years instead of 21 years.

Any suggestions?
Thanx in advance......Ltat42a

[Cutter]

Good morning Ltat42a

For #1 do this:

=IF(B2="","",B2)

For #2 do this:

=TRUNC(DATEDIF(A7,TODAY(),"m")/12)

[Ltat42a]

Whew! That works great - THANX SO MUCH!!!


...Ltat42a

[Sandy Mann]

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>

> =DATEDIF(A7,TODAY(),"m")/12.

If you want the answer in whole numbers why calculate it in months first?

=DATEDIF(A7,TODAY(),"y")

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Hi...maybe you can help.
> I made a spreadsheet to track monies spent on overtime. I have over 20
> people that I'm tracking for. I have their name, number of hours, their
> hire date, and their hourly rate.
>
> I need help with two items -
> 1. I have a section of the spreadsheet that summarizes everyone's name
> and the entire amount spent on OT. I'm using the formula =B2 to copy
> their name from cell B2 to L121. If there is no name listed in B2, L121
> returns a value of 0. How can I show L121 as blank instead of 0? If a
> name is listed in B2, then it will appear in L121.
>
> 2. I'm using a date calculation to tell me how long they have been
> employed -
> =DATEDIF(A7,TODAY(),"m")/12.
> This gives me their time in service on the job. It gives me the
> calculation in a 2 decimal format. I need it in a whole number. When I
> change the format to a whole number, Excel rounds the number up if the
> value is more than .50 (i.e. 20.75 is rounded up to 21). How can I keep
> Excel from rounding the number up?
> 20.75 years should read as 20 years instead of 21 years.
>
> Any suggestions?
> Thanx in advance......Ltat42a
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
> http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=466602
>

[Ltat42a]

Ok, I'll try that - Thanx.

I do have a nother question.
I'm using this formula to calculate promotion dates, in cell C22, I have -
=IF(A22<>"",DATEDIF(A22,TODAY(),"m")/12,"")
If no date is entered, cell C22 remains blank. When someone does promote, I'll enter the date, cell C22 will start calculating their time in position.

Now....using my question #2, I want to calculate their promotion in whole numbers, but, I want the cell value to be left blank until a date is entered.
How can I do that?

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>

> =DATEDIF(A7,TODAY(),"m")/12.

If you want the answer in whole numbers why calculate it in months first?

=DATEDIF(A7,TODAY(),"y")

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Hi...maybe you can help.
> I made a spreadsheet to track monies spent on overtime. I have over 20
> people that I'm tracking for. I have their name, number of hours, their
> hire date, and their hourly rate.
>
> I need help with two items -
> 1. I have a section of the spreadsheet that summarizes everyone's name
> and the entire amount spent on OT. I'm using the formula =B2 to copy
> their name from cell B2 to L121. If there is no name listed in B2, L121
> returns a value of 0. How can I show L121 as blank instead of 0? If a
> name is listed in B2, then it will appear in L121.
>
> 2. I'm using a date calculation to tell me how long they have been
> employed -
> =DATEDIF(A7,TODAY(),"m")/12.
> This gives me their time in service on the job. It gives me the
> calculation in a 2 decimal format. I need it in a whole number. When I
> change the format to a whole number, Excel rounds the number up if the
> value is more than .50 (i.e. 20.75 is rounded up to 21). How can I keep
> Excel from rounding the number up?
> 20.75 years should read as 20 years instead of 21 years.
>
> Any suggestions?
> Thanx in advance......Ltat42a
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
> http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=466602
>

[Cutter]

Similar to your other one, try this (using Sandy Mann's suggestion):

=IF(A22<>"",DATEDIF(A22,TODAY(),"y"),"")

[Ltat42a]

Similar to your other one, try this (using Sandy Mann's suggestion):

=IF(A22<>"",DATEDIF(A22,TODAY(),"y"),"")

Thanx....that does work good!

I appreciate the help....Ltat42a

[Sandy Mann]

If I follow you correctly try:

=IF(A22="","",DATEDIF(A22,TODAY(),"y")&" Years "&DATEDIF(A22,TODAY(),"ym")&"
Months & "&DATEDIF(A22,TODAY(),"md")&" Days")

which with 15/8/2003 in A22 will return:

2 Years 0 Months & 27 Days

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Ok, I'll try that - Thanx.
>
> I do have a nother question.
> I'm using this formula to calculate promotion dates, in cell C22, I
> have -
> =IF(A22<>"",DATEDIF(A22,TODAY(),"m")/12,"")
> If no date is entered, cell C22 remains blank. When someone does
> promote, I'll enter the date, cell C22 will start calculating their
> time in position.
>
> Now....using my question #2, I want to calculate their promotion in
> whole numbers, but, I want the cell value to be left blank until a date
> is entered.
> How can I do that?
>
>
> Sandy Mann Wrote:
>> "Ltat42a" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>>
>> > =DATEDIF(A7,TODAY(),"m")/12.
>>
>> If you want the answer in whole numbers why calculate it in months
>> first?
>>
>> =DATEDIF(A7,TODAY(),"y")
>>
>> --
>> HTH
>>
>> Sandy
>> [email protected]
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Ltat42a" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > Hi...maybe you can help.
>> > I made a spreadsheet to track monies spent on overtime. I have over
>> 20
>> > people that I'm tracking for. I have their name, number of hours,
>> their
>> > hire date, and their hourly rate.
>> >
>> > I need help with two items -
>> > 1. I have a section of the spreadsheet that summarizes everyone's
>> name
>> > and the entire amount spent on OT. I'm using the formula =B2 to copy
>> > their name from cell B2 to L121. If there is no name listed in B2,
>> L121
>> > returns a value of 0. How can I show L121 as blank instead of 0? If
>> a
>> > name is listed in B2, then it will appear in L121.
>> >
>> > 2. I'm using a date calculation to tell me how long they have been
>> > employed -
>> > =DATEDIF(A7,TODAY(),"m")/12.
>> > This gives me their time in service on the job. It gives me the
>> > calculation in a 2 decimal format. I need it in a whole number. When
>> I
>> > change the format to a whole number, Excel rounds the number up if
>> the
>> > value is more than .50 (i.e. 20.75 is rounded up to 21). How can I
>> keep
>> > Excel from rounding the number up?
>> > 20.75 years should read as 20 years instead of 21 years.
>> >
>> > Any suggestions?
>> > Thanx in advance......Ltat42a
>> >
>> >
>> > --
>> > Ltat42a
>> >
>> ------------------------------------------------------------------------
>> > Ltat42a's Profile:
>> > http://www.excelforum.com/member.php...o&userid=24735
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=466602
>> >
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
> http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=466602
>

[Ltat42a]

I used =IF(A22<>"",DATEDIF(A22,TODAY(),"y"),"") for both the time in service calculation and the promotion calculation. I then formatted the cell as a whole number - no decimals, and both work good.

The earlier formula I used that counted months then divided by 12, I got that here on this forum, but the formula above works better.

Thank you all for the tips!

....Ltat42a