# using a formula to find number of days prior to a lost time occuring

• Hello, i am having issues trying to copy over the total amount of days that were accumulated prior to a lost time incident, when a lost time incident has occured. see below

this is a snap shot of just the heading in row 1 i wanted to show where i want information to be provided. I will have another snap shot below with formulas i was working on.

**Note: cell O1 formula works to provide the total current lost time days from todays current date to the start of the report.

The formula in Cell O1 is : =MAX(0,NETWORKDAYS(IF(COUNTIF(K4:K1000,"Lost Time"),LOOKUP(2,1/(K4:K1000="Lost Time"),E4:E1000),E2),TODAY()-1))

***The formulas i am trying to figure out are:

Cell S1 - to show the highest number of days without lost time

Cell U1 - this will show the total in cell O1 when a lost time has occurred, so it will display 28 days in U1 when cell O1 has reset, as in if today was a lost time and the total in O1 would be 0

Cell S1 - to show highest number from column AM, formula is =MAX(AM4:AM1000)
Cell U1 - to show the total from cell O1 when it has reset - Formula needed

This is the same sheet as above but i hid a bunch of columns to get all information relevant to appear.

i will explain what each column with information in is

Cell E2 - The report start date

Column E - whenever an incident occurs, the date of that incident is put here

Column K - where the severity is entered. The formula above for S1 pulls from column K and E

**Note: information is uinputted from row 4 - row 1000

**Coulmn AK - provides the total of work days between the 2 dates. The formula starting in cell AK5 is =MAX(0,NETWORKDAYS(E4,E5)-1). Cell AK4 formula is =MAX(0,NETWORKDAYS(E2,E4)-1), this is because E2 would be the report start date and AK4 is first row of data entry.
**Column AL - Shows column K Severity - =IFS(K4<>"Lost Time",K4,K4="Lost Time","Lost Time"), i probably didnt need this column or could have just used the copy function instead of formula to copy over the data

**Column AM - This is where i want to determine the amount of days that have occured without a lost time. The formula in each row is

=IF(AL4="Lost Time",AK4), which works if all rows have a lost time. i then put this formula is cell AM - =IF(AL4<>"Lost Time",AK4), which then transfers over the days from column AK.

***So this is what i have. i am not sure where to go from here, this is alot of information and i hope i explained it right. My biggest concern to figure out i guess would be cell U1, cell S1 will always display the biggest number so if i could get help with cell U1 that would be great, or if i am totally out to lunch please let me know. Thank You

• Does this Array* formula work?

=MAX(0,NETWORKDAYS(IF(COUNTIF(K4:K1000,"Lost Time"),LARGE(IF(K4:K1000="Lost Time",E4:E1000),2),E2),TODAY()-1))

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Thanks for responding quickly, that formula works when there is not a Lost Time in column K, Once i put a lost time in, the result is an #NUM! error

• Lets say a lost time occured on October 23, 2017, there should have been 36 LTI free days - from Sept.1 - Oct. 23. Now if i have another lost time on November 8, 2017 will that formula display the days between Oct.23 - Nov 8 which would be 12? Thanks

• Did you try confirming with CTRL+SHIFT+ENTER?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Lets say a lost time occured on October 23, 2017, there should have been 36 LTI free days - from Sept.1 - Oct. 23. Now if i have another lost time on November 8, 2017 will that formula display the days between Oct.23 - Nov 8 which would be 12? Thanks

No it won't it will display from 1st day to second last "Lost time" date.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• yes i used ctrl shift enter and works until a lost time is entered

• If you attach a sample workbook it may help to get you a better result.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• here is a sample sheet, please follow my previous concerns from original post to see what i am trying to accomplish. Thank You

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!