Days On Hand Calculation
Jan 29, 2009
I'm looking to create an inventory days on hand calculation (DOH), but I'm not quite
sure what formula will do the trick. Here is an example:
DOH......FG Inv......1/29/2009......1/30/2009......2/2/2009......2/3/2009......2/4/2009
X..........80.............20................20.................20..............20..............20
I'm trying to solve for 'X'. My finished good inventory (FG Inv) is 80pcs.
That will cover customer demand out to 2/3/2009. Therefore, I have 4 DOH
of inventory.
I'm looking for a formula to determine the 'DOH'.
View 9 Replies
ADVERTISEMENT
Jul 12, 2012
I am having trouble finding the days on hand for each SKU. I am trying to get a formula to look at how many days will the total SKU Qty cover in the Forecast_qty. I used a vlookup to make sure the sku = sku but i cant get the days on hand number.
SKU
Total
DOH
ITEM_CODE
FORECAST_DT
FORECAST_QTY
596459
450
?
596459
7/11/2012
54
[Code] ............
View 1 Replies
View Related
Jul 22, 2007
If Invoice date is : 17.06.2007 & payment due date is 02.08.2007
In above please confirm formula of days calculation between above two date.
View 12 Replies
View Related
Dec 4, 2008
I am not able to calculate TAT(Turn around Time) between two dates without taking Off days. Plz help me on this.. File is attatched as per example..
View 6 Replies
View Related
Feb 5, 2013
I am trying to figure out the calculation of cycle time from my worksheet. I have 1419 hrs equals 59 days 03 hrs.
If A2=1419
I tried this formula
=INT(A2/24)&" days " &MOD(A2,24)&" hrs"
Which gave me an answer of 59 days 3 hrs.
I was wondering how they got "03 hrs"?
View 9 Replies
View Related
Feb 6, 2008
I would like to calculate how many days past since the special Cell have been filled with data.
For exampl:
If Cell A1 filled with the data in Cell B1 I need to show the day number, if I close the file and open it next day it will show number 1 as a one day past.
View 9 Replies
View Related
Jul 1, 2014
I have to do a calculation that uses the number of days of the previous month. Thought this would work but it doesn't.
[Code].....
View 12 Replies
View Related
Jan 5, 2013
Working on Excel 2010
Need a formula to calculate the number of leave days.
C3 - Start Date, e.g. 01/07/2011
D3 - Calculation date, e.g. 31/12/2012
E3 - Number of months - ??????? Forumal need from 01/07/2011 to 31/12/2012
F3 - Days per month, e.g. 1.25
G3 - E3*F3
View 6 Replies
View Related
Aug 3, 2006
What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.
I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"
B1 is the current days date
B2 is the entered date
What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
View 3 Replies
View Related
Oct 30, 2013
I'm currently trying to set up a sheet ...
When setting up a Direct Debit There are 3 options, the 7th, 17th and 27th. I've put these into a drop down list. I need this list to be slightly dynamic. I must allow 10 working days from the date of the arrangment before I can apply the DD.
To explain further. If someone were to want to set up a DD and spoke to me today. If they then requested the DD be taken on the 7th, I wouldn't be able to set it up until the 7th of December as 10 working days are required. I'd like the list to reflect this. I can do it in OO with a simple if / switch but excel is a strange creature and I'm not sure how to approach the problem.
From that I think I also need to calculate the number of weeks remaining in the current financial year. I think I should be able to do this by using the Today() function and some maths.
View 1 Replies
View Related
Dec 19, 2008
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 Related
Sep 22, 2009
I have a simple inventory spreadsheet that is doing everything I need minus one function. I have not been able to figure out how to make this work yet, maybe I can get some help. We remodel apartment complexes. I purchase material and hand it out to the contractors. I am using one page to track all movement of the items, but I have another page that I want to do that math and tell me what I should have on hand. Example: (On sheet1 "movement") I hand out 20 outlets. On my inventory on hand sheet I have a line that says "Outlet", the current quantity is 40. I have a drop down list so that on the Movement page I select "Outlet" in column A and in Column B I put in the quantity. I want to keep a running total on my IOHand page of everything, so that it would automatically subtract the 20 I just handed out and leave my IOH as 20. I have about 100 items on my IOH page. I assume I need a formula in the quantity cell of my IOH page that will search the Movement page and look for a match to what is in column A and Sum all of my "outlets" or "sinks" into the right cell.
View 7 Replies
View Related
Nov 22, 2009
I have to enter a 2D array by hand into code because I am doing it in PowerPoint VBA, but I can't find out how to enter a giant array manually.
I know I could do it like this:
MyArray(0,0) = 1
MyArray(1,0) = 2
MyArray(2,0) = 3
...
MyArray(99,99) = 10000
But obviously that would be a giant mess.
Couldn't I just define it like:
{0,1,2,3,4,5,6,7,8,9,...,99,100;
101,102,103,...,199,200;
...10000}
View 9 Replies
View Related
May 5, 2014
I have created "front page" which allow user to enter their enquiry.
The formula will be as per below.
Issuing
World Bank -->if amount less then 500,000 = amount x 0.03125%; if amount from 500,000 - 2.5mil = amount x 0.015625%
Royal Bank -->amount x 0.0625% x no. of month (min charges 100)
Deutsche Bank -->0.025% per 3 months (min. S$50)
Stand By LC
World Bank -->0.02% per quarterly (3mths) - Amount X Rate X 1 (1 to 3mths) or X 2(4 to 6 mths) or X3 (7 to 9 mths) or X4 (10-12 mths)
Royal Bank -->0.25%p.a by day (Principal x Rate x Day divide by 360 day x 100)
Am I able to set a formula for example:-
1. When I click on Mode of payment - Issuing
2. On my right hand side under column "Bank Name" will list down all bank available
3. On my right hand side under column "Estimate cost" will auto calc the cost base on the bank's formula.
View 1 Replies
View Related
Jan 7, 2010
Does anyone now if it's possible to freeze left and right hand columns simultaneously eg. cell C3 above and left AND cell DB3 above and right?
The columns on between would then scroll left or right, until the static column was reached?
View 14 Replies
View Related
Jan 22, 2008
I'm using commandbutton in a worksheet, and would like it to have the same cursor displayed as the 'forms' control.... for some reason, it remains an 'arrow' when the forms becomes a hand. I've identified the 'mousepointer' property, as well as the 'mouseicon' properties; however, it appears that for some reason, I don't have the hand-cursor icon on my system. (i've searched the hard disk, and apparently this is not entirely uncommon according to some posts I've found around the internet).
So my question/favor to ask is:
1) where can i find the standard windows xp system scheme "link select" hand-cursor
(control panel->mouse->mouse pointers... last one)
2) I believe its filename is "hand-l.cur"..... if anyone reading this post can quickly check their windowscursors directory for this file, and reply/ upload it
View 3 Replies
View Related
Mar 3, 2008
I'm trying to get the cursor to turn to the hand (with the index finger pointing) when the user rolls over a label.
View 4 Replies
View Related
Dec 14, 2008
The questions are shown on the attached spreadsheet. There are 2 questions one on each sheet. On the spreadsheet the yellow cells indicate data that I'd import ( on the left) and input by hand to get the process going. The green cells have the correct numbers in them but I've had to manipulate them by hand.
View 5 Replies
View Related
Mar 10, 2014
I have csv file with tab delimited fields (see attached blacklist.csv). 3rd and 4th fields are in format dd/mm/yyyy hh24:mi:ss, some fields can be zero. I need to split data to columns and display datetime fields in format dd.mm.yyyy hh:mm:ss.
OK... open csv in Excel, select column A, press button "Split in columns", select tab as delimiter, choose date format (DMY) for 3rd and 4th field and press OK. Test is splitted into columns, now select columns C and D and select desired format "dd.mm.yyyy hh:mm:ss". Then I select columns A:E and set column widths to fit data in columns. This all is OK and without problems (see attached file Handmade.xlsx).
But now... Now I record all previous step into macro. There is macro text: [Code] ....
Now I copy macro text into clipboard and open blacklist.csv again. Insert button to list and bind it to new macro. Paste text of macro from clipboard. (See attached file blacklist_beforeClick.xlsm). Now click button Btn and voila... some dates remain in previous format, some dates seems to be in desired format, but day and month are swapped. Why are there differences when comparing data made by hand and data made by macro??? compare.jpg This is the same behavior in more computers, some use Czech version of Excel 2010, some use English version.
View 2 Replies
View Related
Mar 19, 2008
I have a form where I want to change the color box on the left hand side see attached image. Now it is a static and I want it dynamic where the user chooses the color. Now it is constant using an image with the color green, red, blue, etc... I want a color picker so user can pick their own colors and when the color is picked the code will pick the color number.
View 9 Replies
View Related
Jun 9, 2009
I'm working with text cells I get this tiny indent on the left hand side of a cell about the size of one hit of the spacebar button.
Excel doesn't recognise this as an indent and I can't get rid of it. It's, pardon my french
View 9 Replies
View Related
Jul 31, 2006
Is there a way (in VBA) to position a specific cell of a worksheet in the upper left-hand corner (i.e. cell H40)?? I have some code that I'd like to have do this.
View 3 Replies
View Related
Feb 8, 2008
Need a way to switch the default positioning of pivot table row totals? I would like to left justify them insead of always having the totals on the far right side where they are often out of view to the user without scrolling. I end up having to place 2 pivot tables side by side so that I can make the totals appear first.
View 3 Replies
View Related
Jul 7, 2009
Attached is a copy of my data and i want to find the mode of each months data without typing it out into long hand form, eg. 111122222333344444 etc. so i believe the answer will be some where around 5 for most of them, but i just don't know a formular to show it automatically on the spread sheet.
View 2 Replies
View Related
Apr 11, 2012
I've been given a spreadsheet that is poorly constructed but I'm not allowed to alter.
The layout is:
Box Name Box Date ID1 ID2 ID3 ....
Where there is not a fixed number of IDs in each box
(There are five different sheets so I'll have some fun concatenating results)
Given a list of IDs I need to return the box name and box date that the ID's file sits in. The spreadsheet has blanks everywhere and the numbers contained in rows are non-consecutive and smaller values can appear later in a column than larger ones.
I think I need some combination of index, offset and maybe sumproduct? TBH, I avoid sumproducts like the plague, preferring to utilise sumifs and dynamic named ranges since this improves clarity so I'm basically a newb at them.
Any skeleton framework that you think would work to return the text values for the LHS where the number could be anywhere? Once I can see a rough example I should be able to get to grips with the logic and wrangle it into the necessary solution.
View 5 Replies
View Related
May 27, 2012
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
View 5 Replies
View Related
Mar 14, 2014
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.
View 9 Replies
View Related
Sep 1, 2007
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
View 9 Replies
View Related
Oct 1, 2003
I need to figure out the logic to ignore two cards to make the best possible five card hand from 7 cards total. Has anyone ever done this before?
As I said before, I'm trying to do this in excel only (no vba), so this is much more complicated than usual.
View 9 Replies
View Related
Jan 23, 2009
How can I convert the number of days for example 366 days to years, months, days 366=1year, 0 months, 1 day
View 3 Replies
View Related
