So whats the maximum amount of time it could possibly sit there before it gets sent back or is confirmed or is that not really a thing that can be predicted ?
Ideally it is 14 days by default but there some conditions that could still keep it there more than that time. One is if the wallet doesn’t continually rebroadcast, but if they do then it will certainly take more time. Secondly is since node’s mempools receives transaction in different Time it will also take different Time to drop it and also the size of the pools are different and as such some will keep it beyond that time