I am trying to quantify the number of widgets in inventory that are useable.
Widgets are useable after 30 days from the date of purchase.
So far I have
C2=Purchase Date
=countif(C2:C5000, "="&today(C2)+30)
I am getting a value of 0 which is incorrect.
What i have is a list of dates in a row. I want to set up a countif formula to count the number of date that fall with in 90 days of todays date. I'm planning on placing todays date in a cell in the upper left hand corner (cell A2) above the list of dates. This way i can just type in todays date and the spreedsheet will count the number of dates, in the list, that fall with in 90 days of the date i type into cell A2
View 2 Replies View RelatedAny cleanest way to track the high-water mark of the price of a stock I own ONLY AFTER my date of purchase.
Each day I log on, a VLookup pulls up the recent stock price. How can I create a formula that will store this price and compare it to the stock price the next time I log in and save the highest of the two prices for the next day's comparison.
Day 1 $10
Day 2 $11- The $10 is replaced by $11
Day 3 $10- The $11 is retained.
Day 4 $12- The $11 is replaced by $12.
i am trying to get back to the original purchase date of an asset.
It has being depreciating using the reducing balance method.
I have the Net book value of the asset currently, the original cost and the depreciation rate.
However i do not know the quickest way in excel to work back to the original purchase date.
See attached file for examples and my long workaround to estimate purchase date.
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
Countif Today()+90
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
I am trying to use the following formula to count the number of times a day appears in a column. It doesn't seem to be working the way I want it to. =COUNTIF($A13:$A200,"Sunday"). It could be the way the days of the week are gathered on the sheet, you can see the attached workbook to see how I am using it.
View 4 Replies View RelatedI am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies View RelatedI am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?
View 3 Replies View RelatedI need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
how can i count the positive days to a inserted date value or the past days (negative value) to the system date (today).
View 5 Replies View RelatedI have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.
View 14 Replies View RelatedI have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies View RelatedI have two questions regarding date format and hope you can provide input.
1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?
2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"
View 7 Replies View RelatedI was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.
Example: 04/31/08 - 04/01/08 = 30 (days)
I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008
View 4 Replies View RelatedWe have a specific time frame for WIP and to purchase new supplies before they run out.
I've setup a worksheet with the various materials and time for each. With the help from past post, I was able to return the time frame before our next purchase.
Here's the formula used.
ive created an excel spreadsheet with expiration dates in one row and im trying to write a code which will pull out all information in the column if the expiration date in a certain row is within 90 days of the date the excel file is accessed
View 2 Replies View RelatedI have todays date in cell A2. I have a range of dates in a row 4 and numbers below each date in row 6. What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.
Example......
The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.
I've got a spreadsheet that gets its information from outside of Excel. The Date comes across as a number stored as text. I need to some how calculate the amount of days between this imported date (Settlement Date) and Today's date.
View 4 Replies View RelatedI am trying to enter a formula that will automatically suggest a purchase quantity by comparing the pieces needed against the required package quantity. I have attached a small example workbook, which has a description of the requirements for the formula, but I will also incude that description in this post.
Problem Description:
Our purchasing guideline is that we MUST order product in package quantities, as the vendor won't ship a partial package. The required package quantity for each part is shown in column B. If the pcs needed qty in column A is less than half the buy pack qty, you buy zero. If it is half or above, you buy a full package.
I want to put a formula in column C that will recommend the suggested buy quantity. The formula would need to compare the pcs needed to the buy pack qty, and put the suggested buy qty in column C. It should also consider multiples of the buy pack qty, such as if the buy pack is 5, and the pcs needed is 12, the formula would suggest 10 pcs. If the buy pack is 5, and pcs needed is 13, the formula would suggest 15 pieces, etc. I have manually entered the sugg buy qty for the first 22 rows, to give examples of what value the formula should return.
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
View 4 Replies View RelatedI have a Purchase Order template that I created in Excel and an Excel Customer spreadsheet. I am trying to get the Customer Name, Address, City/State/Zip (all found in different columns on the customer spreadsheet) to autopopulate on the Purchase Order when the matching phone number is entered. New to advanced formulas but looking for something like: if the phone number on the purchase order equals the phone number on the customer database, bring over the Customer info above to the appropriate cells on the purchase order.
View 5 Replies View RelatedI have been sent a price list from a supplier and need to create an order form that can be populated from the price lists. I have to pages of price list as well. I will also be adding to these as I start getting other supplied product and need my client to be able to generate a order from these in the simplest way possible. Again I hardly use excel and these docs are all sent in excel format.
View 4 Replies View Relatedi have a purchase order sheet
firstly i have attached said spreadsheet
1st sheet is named purchase order
2nd sheet is named purchase order numbers
upon opening the spreadsheet i would like on sheet purchase order to automatically input the next available number from column a in sheet purchase orders ( in this case next available is st010 and put that into cell k12 on sheet purchase order
i then want to enter the relevant data in sheet purchase order cells
b16 , b23 , g23 , i23 and d28
i then want to click a button with macro attached to transfer that data in to sheet purchase order numbers
I trade the stock market and am trying to get excel to automatically calculate the required quantity of shares to be purchased in order to achieve a particular breakeven point. Question: How many shares required at a price of 37.30 to achieve a breakeven of 36.24 when I already have 8000 shares at a price of 34.73? I'm looking for the formula to solve this not just the answer.
View 9 Replies View RelatedI need to create a purchase order page whereby each item is then transposed into seperate sheets detailing the product along with price etc. I need the po number to be automatically generated to follow on from the previous therefore giving us a continuous po thread. I also need the purchase order as typed to be saved into a seperate sheet / file each time so that the po is always blank when someone else creates a new post
View 4 Replies View Related