Create Machine Utilization on a yearly calendar

Hello,

I am very new to Excel, but I want to create a machine utilization visualization calendar.

We currently have 3 machines and I want to visualize which machine was(will be) busy at what day. We receive a Po which sets the received Date, and there a due date given on it. for current jobs, I would like the calendar to show that the machine 1 is busy between PO received date and due date. but once I enter the shipped date the calendar should update to show machine 1 was busy from PO received date to shipping date.

Note: I have a drop down selector for all 3 machines. All three machine are separate from each other and doesn't correlate to each other and date will overlap each other, but it will be separate PO.


Example: 1st PO was received on 29th January 2021 and due date was 28th February 2021. Machine name for this job was F900. So yearly calendar on the other sheet should highlight the cells between 29th January to 28th February. Although I shipped that job on 27th February, so after I put the shipping date, it should not show the machine busy on February 28th.

I have attached a screenshot of Sheet 1, where I will input all numbers. and Sheet 2 is the format I have come up with to show whole year in one screen. I have 3 cells for 1 date for each machine. Second sheet can be changed as long it serves the purpose.(this is my first post and I couldn't attach anything to this so I have replied to my post with the excel file)

I have done my best to explain the situation but let me know if anything is missing or not clear.

Thank you in advance for helping.

Regards,
Jay

Welcome to the forum

Rather than reinvent the wheel you might want to look into existing solutions. For example, Pete_UK has created a number of calendar variants and posted them to this forum.

Here is one example that that seems to me to be close to your needs. Your "printer" would map to "Activity".

https://www.excelforum.com/excel-for...ta-ranges.html

Let us know what you think

OK, I have revised (butchered?) the Pete_UK calendar that I referenced above.

The "Activities" worksheet (which is part of the calendar) is automatically populated from your "Sales" worksheet. I have also incorporated the "Shipped date" logic that you note above. In summary there's no manual entry needed at all on the "Activities" worksheet (other than to make sure that the formulas are propagated down sufficient rows.

I have also rearranged the calendar to show a whole year at a time rather than just a month at a time.

I'm not certain what you want to display in the calendar: PO number or printer name or something else. For now I have assumed printer name.

See if you think this is getting closer to what you want. Let us know.

just three words. OH MY GOD.

My man, this is exactly what I needed. Thank you very much.

You have helped me a lot, but this might be a silly question, but is there a way to keep 1 printer on one row only. For example, row 7, 11, 15 and so one should only be used by F370 and not the other two printer??
To answer your question, I was gonna use a cell coloring to differentiate all three printers. I think I can do that with Conditional Formatting. Like Green for F370, Red for F900, and Blue for GE M2.

Thanks for the feedback and rep!

I think I have done what you want in the attached update. I have introduced a table on the Activities worksheet at M1:N5 that maps the printer name to a line number (1, 2 or 3). If you want a different order than that I have provided then simply edit this table.

The only change I made was to the formula in col-K of the Activities worksheet. In K1 copied down:

Formula:

Please Login or Register  to view this content.

Hopefully this is what you were looking for.

Yes, conditional formatting should work fine - you should be able to do the whole PrinterUtilization table with just one set of 3 rules.

Thanks for marking it solved. Just a couple of things in closing:

1) The Activities sheet formulas in cols A:K extend only to row 129 in the file I provided in post #7. The way the calendar works is that it needs one row on the activities sheet per day per printer of printer utilization, so if you come close to 100% utilization of 3 printers over one year then you would need to copy the formulas to about 3 * 365 rows. There is no problem in extending the formulas beyond what you actually need.

2) I'm not sure how you plan to maintain the Sales worksheet. It is doable via copy/paste, but if you delete one or more rows you will get #ref errors on the Activities sheet. Se if it is workable as is for you but I can, if necessary, do the linkage more robustly if it is needed - let me know.

Geoff

OK - sounds good.

@Pete_UK: I was hoping you were going to jump in earlier! Hopefully I didn't butcher your calendar too much

Cheers Pete - and thanks for the rep.