I need a formula that will calculate a past date. This past date must be 40 buisness days from the current business day.
Simple Example: =TODAY()-40
The Simple Example does almost what I need but I need it to count 40 business days (excluding Weekends and Holidays, if holidays can be included). The Simple example counts back 40 business days including weekends and holidays.
As always,
Thanks in advance
Re: COUNTIF formula with multiple Arguments
Thanks guys, that worked, in my actual spreadsheet I'm working in two worksheets and using the formula you two suggested I was always getting 0, then I noticed I had quotes around my cell value, once they were removed it calculated properly.
thanks again.
Just curious: couldn't this also be done using the Count formula? Just an FYI for me, thanks.
I'm trying to calculate the number of itmes in one Cell that also has a specific values in another Cell.
Example: I have a column for Job Type, F. Name, L. Name, and Age. I want to create a formula that displays the number of times a particular Job Type and F. Name displays in the columns.
I've attached a sample spreadsheet. In it I've listed in Cells F3 - F6 what I'm trying to accomplish and in Cels G3 - G6 were my attemps (most failed), I was able to use the CountIF formula to calculate the first one (F3/G3) but, the others are not as easy because they require two values be specific.
As always, thanks in advance for your help
Re: Autofilter on specific columns
Please forgive this post, I was able to answer my own question. But for anyone that needs to know how, simply select the two column headers and select Autofilter.
I have a worksheet with several columns and I'm protecting it from edits, but I want to allow users the ability to filter on only two of the columns, how can I do this? (Auto Filter adds the filter option to all my columns)
Re: Leading Zeros in a Cell
Will, sorry about that last message.
I'm trying to create a File formatter using excel. I'm using the formula "& <Cell> &" to allow the users to entered the needed parameters. But some of the paramers are totals and must be 10 digits in length (leading zeros are used if needed, example: 0000007054 instead of 7054). The custom format works great in the cell that I'm doing the actual totals but in the cell that I reference that total using the "& <Cell> &" it reverts back to 7054 instead of 0000007054. I'm trying some of the suggestion that were posted after I signed off yesterday. Thanks Will in advance for any advice and my apologies if I wasn't very clear.
Re: Leading Zeros in a Cell
That worked for the individual cell but in the cell I use the "& A1 &" it reverts back to 7054 instead of 0000007054.
Is there a way to format cells to except leading zeros?
I'm using the "& A1 &" formula to help created a specific file format. The file format requires that some values require a certain length and if the value does not meet this length then the value should be proceeded by Zero.
Example: Require characters is 10, but the values is 7054. 7054 would be enter into the file format as 0000007054.
How can or can I format excel utilize leading zeros, other than making it a text cell because the value must be calculated?
Re: Greater than Date timestamp Query
thanks Will, I'll give it a try
I'm need a SQL query that will display only records with a last modified date earlier than a specific date.
Example: I want to locate all records with a last modified date earlier than 12/05/2005 (Examples: 11/20/2005, 12/01/2005, etc.)
How can this be applied using a Where clause?
Re: IF Formula for amount of Characters in a Cell
Max, To answer your question "Why allow unnacceptable char length however?" the some values hard values that I don't want the user to enter anything more or less than the required and I used the Data Validation, in one specific area I want to allow the user to enter any value but be shown the values is not the correct length.
Thanks again.
Re: IF Formula for amount of Characters in a Cell
Thank you (Wigi, Max, and Bob)
I used a combination of the Data Validation and LEN to accomplish my goal.
thank you so much for your help. again.
I need an IF formula or maybe even another logic formula value that will display one of two values (acceptable, unacceptable) if a cell had a set number of characters.
Example Scenario:
I have 4 cells that require the following:
Cell B1 requires 9 charachters
Cell B2 requires 16 characters
Cell B3 requires 20 characters
Cell B4 requires 10 characters
I want Cells C1 - C4 to display Acceptable or Unacceptable based on the amount of characters in cells B1 - B4 (if there are 5 characters in B1, C1 should display unacceptable)
Note: Can C1- C4 also contain a Conditional Format (when its acceptable the cell is green and if its unacceptable the cell is red, both with a white font for visibility).
Re: Conditional formatting using an ISTEXT formula
Biz and Bob, I was able to make it work, thanks for your help.
I want to add a conditional format to a column that will format the cells in that column if text is entered into the cell.
Can you use the (ISTEXT) formula without arguments here or how could you perform the actions listed above?
Example: while the cell is blank the cell does not have any fill, but when text is entered the cell becomes yellow with bold black font.
Sample Spread sheet attached.
Re: Turning off water marks
Thank you that was it
I'm working on a template that my teammate created, he created it with a page # watermark on each worksheet, how can I turn this off, its distracting when trying to work with the worksheet.
Re: (IF(ISTEXT), "1","0") values, not seen as numbers for a SUM formula, can this be done
=IF(ISTEXT(A1),1,0) worked perfectly, thank you.
I need a formula that will do the following:
If text is in a certain cell display the number 1 if not display the number 2 (I was able to accomplish this with the formula (IF(ISTEXT(A1),"1","0"), after this value is configured, I want to add up the total number of 1s, to display in another cell, I tried to do a simple "=sum(B1:B20)" but the excel is not recognizing the 1 and 0 as numbers (I also tried formatting the cells for numbers but this didn't work either.
My objective: I want to record and total the number of objects in a collection, I thought each time I entered the name of one of the object in column A, a 1 would populate in the hidden B column, then I'd do a sum of the B column to get my total number of objects.
My Problem: Sumn does not recognize the IF(ISTEXT) formula value as a number.
Can my original concept work or should I try another method?
I've attached a sample file, you may need to unhide the B column.
Re: SUMIF formula for subracting
Thanks Derk, that worked (guess I was trying to complicate the equation, thanks again).
