Jun 23 2020 11:15 PM
hi,
I've attached my production plan file. i need to highlight cells based on shifts taking for the production. as a example if i have 100,000 pcs to produce and my capacity is 250,000 per shift. it means i need 4 shifts to production. i need to highlight 4 cells Based on days to be taken for production. please refer xlsx file
can anyone help to me?
Jun 24 2020 01:56 AM
@Ravindu94 See attached. Cells with blue text require input. All others calculate automatically. Note that I use real dates in the column headers, aligned to center across two cells (i.e. NOT merged). Start date includes a time element as well to help calculate the end date and time in F. The example only covers 4 days.
Rather than applying multiple conditional formatting formulae, I chose to test the condition in columns G:N, resulting in TRUE or FALSE (formatted white text on white background), thus invisible and then apply conditional formatting on the entire row area with one simple rule (TRUE = Green text and background).
Perhaps feasible in your situation. If so,I trust you can apply this method to your own schedule yourself.
Jun 24 2020 02:30 AM
@Riny_van_Eekelen thank you very much for your respond . its really good. i have small trouble. if 1st production end at day shift how can i start same day night shift production
Jun 24 2020 03:14 AM
Solution@Ravindu94 Changed the formula a little bit. Forgot to add "+12/24" to the first part of teh Night-shift formulae. See attached
Jun 24 2020 05:00 AM
@Riny_van_Eekelen many thanx you're great
Jun 24 2020 03:14 AM
Solution@Ravindu94 Changed the formula a little bit. Forgot to add "+12/24" to the first part of teh Night-shift formulae. See attached