#### Help with two items

```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?

Any suggestions?

--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24735

```
 0
9/11/2005 12:15:55 PM
excel.misc 78881 articles. 5 followers.

8 Replies
489 Views

Similar Articles

[PageSpeed] 59

```Good morning Ltat42a

For #1 do this:

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

For #2 do this:

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

--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9848

```
 0
9/11/2005 12:36:27 PM
```"Ltat42a" <Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com> wrote in
message news:Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com...
>

> =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
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

"Ltat42a" <Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com> wrote in
message news:Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com...
>
> 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?
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=24735
>

```
 0
sandymann2 (1054)
9/11/2005 1:30:22 PM
```Whew! That works great - THANX SO MUCH!!!

...Ltat42

--
Ltat42
-----------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2473

```
 0
9/11/2005 1:45:47 PM
```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" <Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com> wrote
> in
> message news:Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com...
> >
>
> > =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
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
> "Ltat42a" <Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com> wrote
> in
> message news:Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com...
> >
> > 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?
> >
> >
> > --
> > Ltat42a
> >
> ------------------------------------------------------------------------
> > Ltat42a's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=24735
> >

--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24735

```
 0
9/11/2005 5:36:23 PM
```Similar to your other one, try this (using Sandy Mann's suggestion):

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

--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9848

```
 0
9/11/2005 7:08:29 PM
```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
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

"Ltat42a" <Ltat42a.1v7hyc_1126461903.1141@excelforum-nospam.com> wrote in
message news:Ltat42a.1v7hyc_1126461903.1141@excelforum-nospam.com...
>
> 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" <Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com> wrote
>> in
>> message news:Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com...
>> >
>>
>> > =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
>> sandymann@mailinator.com
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Ltat42a" <Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com> wrote
>> in
>> message news:Ltat42a.1v742a_1126443905.4571@excelforum-nospam.com...
>> >
>> > 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?
>> >
>> >
>> > --
>> > Ltat42a
>> >
>> ------------------------------------------------------------------------
>> > Ltat42a's Profile:
>> > http://www.excelforum.com/member.php?action=getinfo&userid=24735
>> >
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=24735
>

```
 0
sandymann2 (1054)
9/11/2005 7:30:41 PM
```Cutter Wrote:
> 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

--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24735

```
 0
9/11/2005 7:45:31 PM
```I used =IF(A22<>"",DATEDIF(A22,TODAY(),"y"),"") for both the time i
service calculation and the promotion calculation. I then formatted th
cell as a whole number - no decimals, and both work good.

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

Thank you all for the tips!

....Ltat42

--
Ltat42
-----------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2473

```
 0
9/12/2005 12:25:53 PM

Similar Artilces:

Help
In the last three days, I have unable to download attachments. Previously I had no problem. When I attempt to open my attachments, I get a window called File Conversion filled with ASCII characters. Need info on how to correct this. It sounds as if these attachments are defaulting to Microsoft Word to open. What types of files are these? What are the file extensions? -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "Manville" <bjmanville@sbcglobal.net&g...

i need to access the exchange server remotly. we have a vpn set up and i don't know how to get in to do simple things like delete the bad mail folder ect.. or access simple files. if some one could guide me the right way or help it would be muc appriciated. thanks tony Have you considered remote desktop? Nue "tmolaee" <tmolaee@discussions.microsoft.com> wrote in message news:4E47D75F-8825-4766-A4B1-49573815A158@microsoft.com... >i need to access the exchange server remotly. we have a vpn set up and i > don't know how to get in to do simple things like dele...

Query using two separate query totals
I'm trying to create a query using a table of user information and two separate queries with totaled hours over a period of time. Each separate query has a total number of hours used between a start date and end date. It is possible that a user can use hours from either query. I would like to be able to have the totals information from each hours query sorted by the contact's last name. I so far have the contact's Name, the total hours from one query, and the total hours from another query. When I run the query, it shows the information from the first query and c...

Supplemental info to "HELP-excel date malfunction
Here's some additional info to the earlier post on the date cell function malfunction. Been isolated to the function to insert the date (cell format/date). It seems the application cannot interpret the system date properly. Always inserts/interprets the date as CurrentMonth-01-2011, instead of actual date. I cannot use the application this way. I am using the 'general' setting in cell format, then entering the string Month(abr)-day [eg= dec-17] instead of letting application enter the date automatically. Is there a patch available for this issue? -- Visit Family Rad...

HELP!!!!!!!
Hello! I'm doing a sheet at work and I'm having some problems, I need your help! Whay I have is a sheet with some columns and rows. - 1: Can I put "filters" on SOME columns only? I can put filter with auto-filter to all columns, but it makes no sense... How to put on SOME only? - 2: Can I auto create a book when inserting some data on a cell (write down something on a blank cell)? use the same name as text written on cell? - 3: Complete some cells auto from specific cells on other sheet (file) of excel? - 4: Hide some columns so some users on a Domain don't see......

help
how to enlarge publisher business card to letter size 8.5x11 A single card? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "notoes" <notoes@discussions.microsoft.com> wrote in message news:09072BB4-4BB5-45E1-973A-32D0538D4999@microsoft.com... > how to enlarge publisher business card to letter size 8.5x11 must have a big wallet to keep them in -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "JoAnn Paules" <jl_paules@hotnospammail.com> wrote i...

Urgent Help Required on Excel Macro Problem
Hi there, We are facing one problem from around 2 months.Some of our users have XF_SIC.A virus found in their pc.XF_SIC.A virus generally found in excel workbook as macro virus.This virus create one excel 4.0 macro sheet with "very hidden and very protected" attributes in excel 97 workbooks which can't be removed as it is not seen.every time when infected excel 97 file opens it infect the default startup excel file(Book1 placed in XLSTART folder) and then every excel file that will be open will have infection as default file book1 infected.now our antivirus software mcafe...

This Item is a copy of ...
Folks, Saw something completely new to me today. Working with a client and on their SharePoint site there is a document that has a notice highlighted in yellow: ! This item is a copy of http://www.mysharepointsite/clients/abc/example.doc Then there is a link back to source. It points to completely differnt SharePoint server which they access via the internet. How did this get there? They have no recollection. How does SharePoint remember this a link back to a source? Is this a "Send to, Other Location? -- --rms www.rmschneider.com Answering my...

to Import Many items to existing database
Hi, Any better ways to import new items to existing database? I have tried to use QSC import tools but it only work during my testing period only, when I decided to use it for real work, it fail !!! Any suggestion will b very much appreciated. kase you may want to try the Retail Realm Import Utility. Contact me for more info. Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm -- URL http://www.retailrealm.co.uk "kase" wrote: > Hi, > Any better ways to import new items to existing database? I have tried to > use QSC import tools but it only work ...

Help: how to print fill pattern well in any printers
As we know, for display, we can call CreatePatternBrush() to create a 8pixel * 8pixel to fill a region. It is perfect for display in the display device and some printers. But In some high-res printer, the fill pattern is printed as gray. (the fill pattern is printed so densy that it looks like a solid gray block, for example, some HP printers and Acrobat PDF printer). I have tried a bigger bitmap brush according to the printer device's resolution. But It is still bad for some printers. Any suggestion is appreciated. Is there any way that is suitable for any printers? ...

How to access help?
That office assistant thing is useless to me. It says you can switch it off but how? And where do I find stock standard help? Or does that only come up when you've got the paperclip switched off? In which case, again, how do I switch the thing off? How to modify or remove Office Assistant Help in Office 2000 http://support.microsoft.com/kb/203689/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "abrogard" <abrogard@discussions.microsoft.com> wrote in message news:36DF541B-4845-454E-B610-4BA5DFD28952@mi...

Help with RecordSetClone
Hello, this is my first attempt at using recordsetclone to look at the same field on a continuous form and update the field. I have looked at other examples on this forum and put together the below code but I keep getting an "invalid argument 3001 error" when it reaches the first .FindNext. With Me!subfrmPersons.Form.RecordsetClone .Bookmark = Me!subfrmPersons.Form.Bookmark .FindNext Me!subfrmPersons.Form![DOB] MsgBox Me!subfrmPersons.Form![DOB] 'for testing only If Not IsNull(Me!subfrmPersons.Form![DOB]) Then Me!subfrmPersons.Form! [Age]...

Macro-separate different dates with two grey rows
Excel 2000: In column B, there are about 200 rows with dates. The sheet is sorted by date. The dates have a range of about 3 weeks. There may be between 1 and 35 rows with the same date. Is there a way to automatically insert 2 grey rows between the different dates? Example: I enter 21april in row 1, then 22april in row 2, and 2 grey rows separate automatically. Joe Hi Joe try the following macro Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, &...

Little help, 5.5 to 2k3 migration problems (long)
Hi, First post here, so I apologise in advance if I get anything too wrong, or it's too long. I had Exch 5.5 running on a Win2k domain controller, 60 odd users, some public folders, some delegate access etc. I decided to migrate the system to a Win2003 / Exchange 2k3 server, following the deployment instaurations on the CD. *Note: on further investigation on the KB, it seems that I should have run the 5.5 DS/IS constancy checker before the migration to iron out any ACL/AD issues - this wasn't done. I have also discovered that my original ex5.5 site name contained invalid character...

Bills of Materials for buy items
My client requires the ability to store a Bill of Material for a 'buy' item. The item is set-up as 'buy' in item resource planning and Item Engineering. However, we imported a Bill of Material. At BOM entry or BOM view system triggers message 'this item is not a made item' and will not display the Bill of Material despite the existance of a Bill of Material Record. The workaround is to check the 'treat as either' box in item engineering - but, this effects standard cost rollup for items maked as either that are part of a multi-level and complex Bill of Ma...

excel help 04-09-10
I have a table (range a5:s33) that is changing constantly and its being updated. I need cell I1 and I2 to give me the date (I1) and time (I2) when any of the cells within the range are modified....any help?...thanks Try the following Sheets Event-Macro: ----------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, [A5:S33]) Is Nothing Then [I1] = Date [I2] = Time End If End Sub --------------- Micky "jcheko" wrote: > I have a table (ran...

Comparing two worksheets
I need to compare two worksheets to derive the accurals of purchas orders and invoices. I can find the lines and copy them if they are actually in just one of the worksheest. My problem is that I am searching for invoice numbers whiere the same number can appear more than once on both worksheets and in some instances the invoice will only be partly paid so there is a difference in the values in the purchase order side caompared with the same invoice numbers in the Invoice side. I need to be able to capture the difference and put that difference into the accurals worksheet. I tire...

Discounts PerLine Item
Hi! Does anyone know of any third party apps that allow you to select a discount perline item on the Sales Trx Entry window? Thanks -- Marisol Mortera ...

Excel data completely disappeared
Please can someone help me with a problem I am having. I created a spreadsheet with Excel and included a 'comments' column designed to appear with a mouseover. I saved the spreadsheet and tested it out, everything ok ... until I went to view it the next day and every single comment has disappeared !! Even the red triangles have completely disappeared ... this is really weird. Can anyone throw any light on this ? TIA, cool4cats -- cool4cats ------------------------------------------------------------------------ cool4cats's Profile: http://www.excelforum.com/member.php?actio...

Finding unique items in data field for pivot tables
I have a huge pivot table I'm trying to create and for a critical item I select I get the following message: A field in your data source has more unique items that can be used in a PivotTable. Microsoft Excel may not be able to create a PivotTable or may create the PivotTable without the data from this field. Any ideas how I can find the duplicates and create the PivotTable with this field? Hello- Based on the message, 'duplicates' is not your problem, *Unique* items is. Pivot tables are limited to 32,500 unique items per field. So if you're analyzing product sales, for...

Maintaining the same address and calendar on two separate machines.
A friend has Outlook 2003 on his machine at his work office and also in his home Office. He has a Treo 700w smart phone and wants to be able to keep the same address book and calendar on both his machines at the office and at home (I know he will be able to only sync his phone with one computer). Is there an easy way to maintain the same address book and calendar on two machines? Using an Exchange server would be well out of his budget or technical abilities. I'm looking into getting him access to his computer in the office from his home. Thanks. Austin Powers <austinpower...

Help with referencing multiple sheets in formulas
Greetings Xcellers I have a workbook with 32 sheets, The first sheet is a month summary and the other 31 sheets are for each day of the month. The sheets are names month,1,2,3..31 I need to copy information from the daily sheets to create a summary on the month sheet like follows A B 1 Day Turnover ..... 2 1 ='1'!E7 3 2 ='2'!E7 4 3 ... 5 5 ='5'!E7 Is there a way to substitute the sheet name from the column Day instead of manually having to enter each formula. There will be a number of columns and I have a number of similar workbooks to make. IE for B2 a formula l...

Calculating the difference between two times
I need to calculate the difference between two times. I do not have a problem when going from 12:00am to 11:59am. I also figured out how to calculate the differnce between 12:00am to 11:59pm. How do you calculate the difference between two times when it goes over two days. For instance from 10:00am (10/03) to 1:00am (10/04). That is 15 hours, but I cannot get that answer. Also, the dates are not important, I just need to be able to calculate that as 15:00 hours. With the start time in A1 (10:00 AM) and the end time in A2 (01:00 AM) use =MOD(A2-A1,1) Regards, Peo Sjoblom &...

Need help with excel sorting/comparing funcations
HI all, the list I am working on consist of phone number, date, conversation start time, conversation end time. and there around 3476 records in one file and 2477 records in another... and they are not in the same format... so what I did was to sort out all the relative infomation in one file by its phone number, and did the same for the other one then I copy and pasted both lists in a new file.. and did the above comparesion.. nothing showed up or just gives me errors.. I am doing some analysis for our datebase records...... the sample I took out is only the small part.. the real f...

VBA Macro help
Hi Friends, I have taken a project using Excel. Now wt i need is that if i click a command button in excel the macro coding must open a excel file from one location and do a some formating (bold the header, sorting by client / stcd / TOS etc in ascending / decending delete columns last action, pat type etc change PN Assign column to Currency insert SIF cloumn before TYPE SRVS .and page formating as landscap, header (XXXXXXXX), page number at the center bottom and save the file name with password (12345) with the filename continuing the current date for example "N0123(curren...