I have four columns in a spreadsheet that refer to a CCTV camera fault
reporting log. The first column is the date the fault is initially reported;
the second is the date that the fault is checked; the third is the date that
the fault is fixed - if the fault is yet to be fixed I have inserted a dash
(alternatively I could leave it blank if that is easier). In the fourth
column I'm trying to use the IF function to calculate the number of days
between the date reported and the date checked (first two columns) if the
third column has a dash (or is blank) i.e. not yet fixed; or insert a dash
(or alternative) if there is a date in the third column i.e. it has been
fixed. Complicated, I know. I hope somebody can get their head round it and
work out an answer for me. Thank you.

Hi Hammond,

Assuming that you have column heading in first row, enter the below formula
in cell D2 and drag it as long as you have data.

=IF(C2="",B2-A2,C2-A2)
This formula will calculate the diff between col 2 and col 1 if col 3 is
blank. It will find the diff betwn col 3 and col 1 if there is date in col 3.

Also make sure that column D is formatted as General and not Date.
if you want to keep col 4 (column D) as blank then try this

=IF(C2="","",B2-A2)
Hope this helps!!
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Hammond-Wandsworth" wrote:

I have four columns in a spreadsheet that refer to a CCTV camera fault
reporting log. The first column is the date the fault is initially reported;
the second is the date that the fault is checked; the third is the date that
the fault is fixed - if the fault is yet to be fixed I have inserted a dash
(alternatively I could leave it blank if that is easier). In the fourth
column I'm trying to use the IF function to calculate the number of days
between the date reported and the date checked (first two columns) if the
third column has a dash (or is blank) i.e. not yet fixed; or insert a dash
(or alternative) if there is a date in the third column i.e. it has been
fixed. Complicated, I know. I hope somebody can get their head round it and
work out an answer for me. Thank you.

Hi,

I lost the logic a bit so 2 formula to try in D1 and drag down:-
=IF(C1="",B1-A1,C1-A1)
This one works out the days between a1 & b1 if c1 is blank or b1 and c1 if
c1 has a date.

=IF(C1="",B1-A1,"-")
Similar but put a dash in D is c has a date

Mike
"Hammond-Wandsworth" wrote:

I have four columns in a spreadsheet that refer to a CCTV camera fault
reporting log. The first column is the date the fault is initially reported;
the second is the date that the fault is checked; the third is the date that
the fault is fixed - if the fault is yet to be fixed I have inserted a dash
(alternatively I could leave it blank if that is easier). In the fourth
column I'm trying to use the IF function to calculate the number of days
between the date reported and the date checked (first two columns) if the
third column has a dash (or is blank) i.e. not yet fixed; or insert a dash
(or alternative) if there is a date in the third column i.e. it has been
fixed. Complicated, I know. I hope somebody can get their head round it and
work out an answer for me. Thank you.

Thank you guys, both extremely helpful. Thanks also from the colleagues who
tasked me with this brain stretcher!

"Pranav Vaidya" wrote:

Hi Hammond,

Assuming that you have column heading in first row, enter the below formula
in cell D2 and drag it as long as you have data.

=IF(C2="",B2-A2,C2-A2)
This formula will calculate the diff between col 2 and col 1 if col 3 is
blank. It will find the diff betwn col 3 and col 1 if there is date in col 3.

Also make sure that column D is formatted as General and not Date.
if you want to keep col 4 (column D) as blank then try this

=IF(C2="","",B2-A2)
Hope this helps!!
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Hammond-Wandsworth" wrote:

I have four columns in a spreadsheet that refer to a CCTV camera fault
reporting log. The first column is the date the fault is initially reported;
the second is the date that the fault is checked; the third is the date that
the fault is fixed - if the fault is yet to be fixed I have inserted a dash
(alternatively I could leave it blank if that is easier). In the fourth
column I'm trying to use the IF function to calculate the number of days
between the date reported and the date checked (first two columns) if the
third column has a dash (or is blank) i.e. not yet fixed; or insert a dash
(or alternative) if there is a date in the third column i.e. it has been
fixed. Complicated, I know. I hope somebody can get their head round it and
work out an answer for me. Thank you.