Time Formula - Modification

[RoscoW88]

Good Afternoon,

Happy Wednesday!!

I am need of assistance as my heads about to fall off....

Some time back some of you lovely people assisted me with a formula for a quandary i was having, which worked perfectly - until i decided to have a play and copy the formula to a similar spreadsheet....

In a nutshell, the idea of the spreadsheet is:
- Column A: Machine Name
- Column B: Order Priority
- Column C: Order No
- Column D: Availability
- Column E: If it is running
- Column F: How Long it runs for
- Column G: The time it started running
- Column H: The time it started / should start based on the job above on the same Machine
- Column I: The time the job should end based on Columns F+H

Now for some reason, row 8 on my example does not have any information in Columns G:I which is correct as the job is not running.
But then in Row 9, it has picked up the End time from Row 7 and calculated based on that....

Rows 8-15 should have no start / end times in them as the job is not running.

But then row 16 is back to running as required.

Can somebody please assist as i am pulling my hair out on this one.

Thanks
Ross

[AliGW]

Are you still using Excel 2016?

[Glenn Kennedy]

You said:

Rows 8-15 should have no start / end times in them as the job is not running

How can you tell that it's not running??

[JohnTopley]

A quick glance sugeests having a "1" in column E for any row which is running is required as having a blank entry in G:I cannot be used as the determinant of "Not running".

[RoscoW88]

Hi Ali, Yes - unfortunately so.

[RoscoW88]

Hi Glenn,

If there is a 1 in column E this shows that the job is running.

Edit:: Only one job can run on one machine at a time.

Thanks
Ross

[Glenn Kennedy]

if the formula in col H is copied down

=IFERROR(IF([@Live]=0,WORKDAY.INTL(TODAY(),1,11)+TIMEVALUE("08:00"),[@SFDC]),"")

does that give you the expected result? What IS/ARE the expected results?

[RoscoW88]

Hi Glenn,

If i copy that formula down it gives me the correct information but for that row only.

On my example sheet the first 5 rows are 'VMC No3'
The first line is in progress and started at 20/09 08:07 and finishes at 22/09 14:36
The second line starts on 22/09 14:36 (the time the first finished) and finishes at 25/09 15:11
and so on until the machine name changes to 'VMC No4' where the formula starts again.

For some reason on 'VMC No8' even though there is not a 1 in Column E, it is producing dates based off VMC No4 finish time.

What i am looking for is that if a job has not started on a machine that no start / end times show against that machine.

Hope that makes sense.

Thanks
Ross

[JeteMc]

Try this modification to the formula that was in cells H3 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.