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


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

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

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

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
[IMG2=JSON]{"alt":"Image","dataalign":"none","datasize":"full","src":"https:\/\/fud.community.services.support.microsoft.com\/Fud\/FileDownloadHandler.ashx?fid=51b63e82f0c9429cbea0d4f818e2835e"}[/IMG2]
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[IMG2=JSON]{"alt":"Image","dataalign":"none","datasize":"full","src":"https:\/\/fud.community.services.support.microsoft.com\/Fud\/FileDownloadHandler.ashx?fid=8c46e20c819344faaaa0fd765218d750"}[/IMG2]
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