The sheet I am creating I have it so that when a cell has a value greater than 0 for a start time (put in 1700) then the cell changes to red. When I add and end time into the cell (1700-1717) it then turns green. Is there anyway to do an if/then either in excel itself or in VBA so that if excel sees me add the “- end time” into the cell it would then add the standard “=” in front of the 1700-1717 to give me an absolute time of 17 minutes in a separate cell while still keeping “1700-1717” in the original cell? The goal is to get a whole column of start and end times and do the simple subtraction in an absolute number to then get the overall average of the start and stop times in a separate cell.

I could probably do this in 2 separate columns but for specific reasons I need to be able to do this in one cell.

I have spent over 45 minutes googling who knows how many different ways to ask and searching stackoverflow without any luck…

I have tried something like this in excel cell itself but they haven’t worked.
=IF(d5=,”-“,=d5)
=IF(d5,"*",=d5)
=IF(d5,"*","=d5")

I have tried in VBA to do something similar to to following as well:
If Range(d5).Value = "<0" Then Formula = "=d5"

I want excel to see me add the “- endtime” into the cell and then take the “start time-end time” and do the simple math in a separate cell while keeping the “start time-end time” still visible in the original cell.