Hello,
I am creating an excel for asset tracking management and I wanted to know the availability for that particular item, if anyone can get a formula for that?
We are sending our item to a project for a certain amount of time, let say A item is sent to project from 5/12/2016 to 5/22/2016 that means that A item is not available, how do we put a formula for that?
Hi -
You could try something like:
=IF(AND(TODAY()>=A1, TODAY()<=A2),"Not Available","Available")
This assumes your two dates are stored in cells A1 and A2.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
Along the lines of loginjmor's solution:
The formula would be dependent on the setup of the spreadsheet. For example the following formula could be used for a set up as shown below:This formula assumes that the item is not available while at the project, however it is available before and after.Formula:
Please Login or Register to view this content.
Excel 2010
Row\Col Item Begin Proj End Proj Available A B C
Sheet: Sheet1
Let me know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
well, the formula worked but i am not sure that is really want I want.
Attached is my excel for asset tracking.
I want to make that Equipment List sheet and Job Status sheet connected.
If the Equipment is used in the project then is unavailable and if we reserved the item it is not available too.
And what if we have new order, i am not sure how we can incorporate the formula you gave me?
Can anyone also came up with the formula with the status of the job, either open, on-going and closed depends on the check-in and check-out date?
Thank you
The following formula in Job Status!C2 and down will fill in the three statuses based on the date:The following formula in Equipment!B2 and down gives the two statuses based on the values that are in the attachment to post #4:Formula:Please Login or Register to view this content.Let me know if you have any questions.Formula:Please Login or Register to view this content.
Hello,
Thank you for the help.
However, can you explain to me about the formula for "=IFERROR(IF(MATCH(A2,'Job Status'!G$2:G$7,0),"Unavailable","Available"),"Available")" ?
I also want to try to understand and learn about it.
And what if I have 2 jobs with the same item. Like one job is done (AAA1006), the item should be available again, and when there is another upcoming job (AAA1007), the item should be unavailable again?
Thank you so much everyone for your help.
Since job AAA1002 had been completed (done) should flow meter 006 be Available?
The formula was written to match the availability statuses that had been put into Equipment!B2:B8 from the attachment in post #4. To do what you are asking, "Like one job is done (AAA1006), the item should be available again, and when there is another upcoming job (AAA1007), the item should be unavailable again?", will require a different formula. Before asking members to start on another formula, please make sure that all foreseeable job status situations have been covered and the availability of each situation has been shown.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks