|
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? 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 |
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...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=466602 |
Whew! That works great - THANX SO MUCH!!! ...Ltat42a -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=466602 |
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" wrote in message ... =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 Replace@mailinator with @tiscali.co.uk "Ltat42a" wrote in message ... 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 |
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...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=466602 |
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 Replace@mailinator with @tiscali.co.uk "Ltat42a" wrote in message ... 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" wrote in message ... =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 Replace@mailinator with @tiscali.co.uk "Ltat42a" wrote in message ... 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 |
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...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=466602 |
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 -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=466602 |
| All times are GMT +1. The time now is 09:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com