Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
Franz, http://www.savefile.com/files/1345175 -- Dipwind ------------------------------------------------------------------------ Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276 View this thread: http://www.excelforum.com/showthread...hreadid=568990 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
Well, Works in a perfection. Thank's very much 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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(G 5,"=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 -- Dipwind ------------------------------------------------------------------------ Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276 View this thread: http://www.excelforum.com/showthread...hreadid=568990 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 -- Dipwind ------------------------------------------------------------------------ Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276 View this thread: http://www.excelforum.com/showthread...hreadid=568990 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |