First things first i correct the typos in the code.

The date in column C is dependent on the formula that you use. I just drop that formula in and convert the result to a value. If you want to see what formula is going in there, comment out the ".Value = .Value" line of code. You can then evaluate the formula.
This bit was key for me as i understand what is happening now after commenting out this line. The function:
=If('Request Guest!D2="","",MAX(0,'Request Guest'!D3-Brain!B$2)*86400) is not being iterated. !D3 inside MAX(0,'Request Guest'!D3-Brain!B$2)*86400) is not changing to !D4, D5, D6 etc.. Do i need to make another formula for the change event?




I guess the reason that you are having problems changing the headings is that the Change Event handler is monitoring the entire columns A to D. Open the workbook without enabling macros, make the changes to your headings and then save the workbook. Open it again, this time enabling macros.

Note: what is happening is that the code uses a variable, tr, for the Target Row. Because the csv doesn't have headers, the row is adjusted using the variable trm1 ... Target Row Minus 1. So, if you try to change row 1 on the Template sheet, the code is trying to write to row 0 which doesn't exist.
This allowed me to fix the headings and I understand the error now.