Random number In calculation

Jack_Rabbit

New Member
Joined
Jan 6, 2025
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

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


Date OpenedDate ClosedDays
01/01/202502/01/2025-1
01/01/202503/01/2025-2
01/01/2025 45658
01/01/202505/01/2025-4
01/01/202503/01/2025-2
01/01/202504/01/2025-3
01/01/202505/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
 
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:
Excel Formula:
=IF([@[Dated Closed]]=0,"",[@[Date Opened]]-[@[Dated Closed]])
which will return nothing if there is no Date Closed.
 
Upvote 0
Solution
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:
Excel Formula:
=IF([@[Dated Closed]]=0,"",[@[Date Opened]]-[@[Dated Closed]])
which will return nothing if there is no Date Closed.
Thank you Joe, much appreciated.
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,226,840
Messages
6,193,279
Members
453,788
Latest member
drcharle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top