+ Reply to Thread
Results 1 to 6 of 6

Calculate deadline based on production capacity

  1. #1
    Registered User
    Join Date
    08-16-2022
    Location
    Spain
    MS-Off Ver
    365 v 2207
    Posts
    3

    Calculate deadline based on production capacity

    Hi y'all,

    I need to calculate a date from now based on the pre-defined "production capacity".
    User manually adds info (cells C3, E3, G3 for example) and based on the information from those cells and predefined production capacity (K 3, K4 - N3. N4), the deadline should appear in cells in column E (Step 1).
    Additionally, the deadlines can only be calculated from hours 8:30 till 17 (L8, N8).

    Then, Step 2 deadline (col F) should add time defined in M11, then Step 3 (col G) - from M12.

    Deadline H column can be ignored.

    Anybody up for this challenge...?
    Thank you all in advance for checking and asking if I missed any important information!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,937

    Re: Calculate deadline based on production capacity

    You don't define a start date and time, nor what steps 1, 2, and 3 are, or what the 5 'tasks' are, or how the values in C3, E3, and G3 are related.... 1500 words would take 1.5 hours, but 25 pages would take 25 hours. So... which would you want to use?

    And example result would be helpful...
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-16-2022
    Location
    Spain
    MS-Off Ver
    365 v 2207
    Posts
    3

    Re: Calculate deadline based on production capacity

    Hi Bernie,
    Thanks for your reply and the questions - and apologies for not being clear.
    Here's more info.
    The start date/time would be "NOW". The deadlines should be calculated based on the production capacity based on the current date and time.
    Steps 1, 2, and 3 are simply different steps of the process. Each of them would have its deadlines depending on the previous ones (Step 2 deadline=step 1 deadline+m11 value).

    Here is an example scenario: the user receives a new project to translate to various languages. Manually inserts values to fields (C3, E3, G3 - those are not related to each other; let's assume sometimes 1500 words are 2 pages, but sometimes 4 - sometimes it's 3 hours, sometimes it's 7. No relation between those values whatsoever).
    The user is also able to amend the general production capacity (O3->Q3, O4->Q4; in the example, the user states that 1000 words take 1h to process).

    Based on the above, excel needs to automatically calculate the deadlines for different steps: if C3=1500, then according to production capacity, time needed=1.5h -> deadline should be calculated to [current date and time]+1.5h.
    Step 2 deadline would be calculated, as per above, adding pre-defined time to S1 deadline.

    Important - the deadlines can be only calculated for the limited hours as mentioned in the original message.

    Thank you again for checking this!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    Re: Calculate deadline based on production capacity

    Hello imtryingmybestok and Welcome to Excel Forum.
    I put a start date/time in cell L15. You could replace that with =NOW() however every time something is changed in the spreadsheet NOW will update, so I would suggest manually entering the date/time by pressing Ctrl+; space Shift+Ctrl+;.
    The formula for Step 1 is: =IF(MOD(SUM(INT(L15),C3/K3*M3/24,MOD(L15,1)),1)>N8,SUM(INT(L15),1,L8,MOD(SUM(INT(L15),C3/K3*M3/24,MOD(L15,1)),1)-N8),SUM(INT(L15),C3/K3*M3/24,MOD(L15,1)))
    The formula for Step 2 is: =IF(SUM($M11/24,MOD(E6,1))>$N8,SUM(INT(E6),1,$L8,SUM($M11/24,MOD(E6,1))-$N8),SUM(E6,$M11/24))
    The formula for Step 3 is: =IF(SUM($M12/24,MOD(F6,1))>$N8,SUM(INT(F6),1,$L8,SUM($M12/24,MOD(F6,1))-$N8),SUM(F6,$M12/24))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-16-2022
    Location
    Spain
    MS-Off Ver
    365 v 2207
    Posts
    3

    Re: Calculate deadline based on production capacity

    Hi JeteMC
    I'm not crying, you're crying!
    This is beautiful. Thank you so much. I think this is exactly what was needed...!
    If there is anything else, I will come back - for the time being marked this as solved
    Have a great Friday and thanks again

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    Re: Calculate deadline based on production capacity

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Production Schedule as per capacity
    By leonsoso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-13-2022, 09:15 AM
  2. capacity for my production
    By Neele in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-03-2021, 05:13 PM
  3. [SOLVED] Calculate monthly production needs based on aggregate production table
    By aroseb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-24-2020, 12:29 PM
  4. Replies: 8
    Last Post: 06-09-2020, 07:17 AM
  5. Calculate percentage increase based on work capacity
    By GregTheHun in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2020, 04:26 PM
  6. Replies: 1
    Last Post: 01-12-2012, 05:26 PM
  7. Replies: 0
    Last Post: 03-16-2005, 03:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1