Does =days() stealing 1 day each time

[msword]

Just noticed that today is not included in the excel day counter, namely cell value equals to zero. Does it mean that 1 day was stealed all these years of using the formula? Is there an elegant way to fix this or remembering to add +1 in the end of the formula is a single option?

Book2
	A	B
1	0	23-Oct
Sheet1

Cell Formulas
Range	Formula
A1	A1	=DAYS(TODAY(), B1)

 

[Joe4]

DAYS is not used to count days, per se.
DAYS calculates the DIFFERENCE between two dates.
If you are comparing today's date, with the TODAY() function, they are returning the same date (10/23).
Since it is the SAME date, there is no difference, so the function returns 0.
If you are trying to use it as a day counter, yes, you will need to always add 1 to your result.

 

[Scott Huish]

You don't really need the DAYS function though, you can just subtract them and format as General.
=B1-TODAY()+1

 

[Joe4]

You don't really need the DAYS function though, you can just subtract them and format as General.
=B1-TODAY()+1

That is a good point. Since dates are stored as numbers, specifically the numbers of DAYS since 1/0/1900, then you can simply subtract the values (provided that your actual date value does NOT have a time component).

 

[msword]

Is there a way to avoid using the B1 date cell altogether and incorporate the date right into the formula?

 

[msword]

If you are trying to use it as a day counter, yes, you will need to always add 1 to your result.

I understand that, but my question is there any suitable formula for the counter creation to avoid adding +1 manually each time?

You don't really need the DAYS function though, you can just subtract them and format as General.
=B1-TODAY()+1

Thanks for bringing this out! I really fed up to manually return cells to the General format each time. This saved me from creating another thread.

 

[Scott Huish]

No, you will have to add 1 to use it as you are describing

 

[msword]

@Scott Huish
Is there a way to avoid using the B1 date cell altogether and incorporate the date right into the formula?

 

[Joe4]

@Scott Huish
Is there a way to avoid using the B1 date cell altogether and incorporate the date right into the formula?

I am not sure this makes much sense. B1 is the date you using in the calculation, comparing to the current date, right?
If you don't have another date, exactly what are you trying to do?

Could you show us a detailed example of exactly what you are trying to do?
Maybe it will make more sense if we can see that.

 

[Scott Huish]

Yes, but as Joe4 mentioned, why would you want to do that?
="10/23/2024"-TODAY()+1