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