New Member
I wish to input a date into a cell but for it to caculat as a 1 in the formula.

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I assume there is more to it than that otherwise you would just replace the reference to the data cell in your formula with a 1.

What are you trying to do and are there any conditions under which the cell would not be treated as a 1

I am trying to enter dates into a coulm that the dates will vary, i.e.

31/12/04
23/6/04
11/11/04

etc the thing that I am trying to do is then add a formula to the bottom cell in the range to count the dates as 1. This will then enable other formulas to use the total. Hope thias makes more sense.

Q: you want the formula to use a 1 if there is a date, and (probably) as a 0 if its not?

=IF(CELL("notation",A1)="D1",1,0)

I'm not sure if "notation" is right but i guessed it cause it is "notatie" in the dutch excel.

if your dates were in A1 to A100

then using =counta(a1:a100) would give you the number of cells with a value in them, hence treating each as 1.

Will this do what you want?

I am trying to enter dates into a coulm that the dates will vary, i.e.

31/12/04
23/6/04
11/11/04

etc the thing that I am trying to do is then add a formula to the bottom cell in the range to count the dates as 1. This will then enable other formulas to use the total. Hope thias makes more sense.

Have you tried...

=COUNT(range)

where range is a reference to the cells containing dates.

Many thanks cherria this is spot on.

Replies
7
Views
139
Replies
1
Views
701
Replies
4
Views
142
Replies
3
Views
84
Replies
1
Views
274

1,196,340
Messages
6,014,710
Members
441,838
Latest member
ykg1991

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.

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

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