Random number In calculation

[Jack_Rabbit]

Hi all,

I have used a formula to calculate the time a Issue was opened. then closed.

Date Opened	Date Closed	Days
01/01/2025	02/01/2025	-1
01/01/2025	03/01/2025	-2
01/01/2025		45658
01/01/2025	05/01/2025	-4
01/01/2025	03/01/2025	-2
01/01/2025	04/01/2025	-3
01/01/2025	05/01/2025	-4
01/01/2025		45658
01/01/2025		45658

But I'm not sure why the 45658 appears, how can I get this to not appear, when 'Date Closed' is empty?

The formula used was =SUM[@[Date Opened]]-[@[Dated Closed]])

Thank you for any help given.

Jack

 

[Joe4]

Welcome to the Board!

Excel stores dates as number, literally the number of days since 1/0/1900.
So the 45658 is a date represented as an Integer (General Number). That is because the blanks are treated as zeroes.

Also note that the SUM in your formula is totally unnecessary. You are not summing anything, you are just subtracting two numbers.

Try this:

=IF([@[Dated Closed]]=0,"",[@[Date Opened]]-[@[Dated Closed]])

which will return nothing if there is no Date Closed.

 

[Jack_Rabbit]

Welcome to the Board!

Excel stores dates as number, literally the number of days since 1/0/1900.
So the 45658 is a date represented as an Integer (General Number). That is because the blanks are treated as zeroes.

Also note that the SUM in your formula is totally unnecessary. You are not summing anything, you are just subtracting two numbers.

Try this:

=IF([@[Dated Closed]]=0,"",[@[Date Opened]]-[@[Dated Closed]])

which will return nothing if there is no Date Closed.

Thank you Joe, much appreciated.

 

[Joe4]

You are welcome.
Glad I was able to help!