# Date Format used in a formula.

##### Board Regular
I can get the date to display a single day through format cell—-

11/14/22 = Monday

—but I can’t use “Monday” in a formula.

I have a form that has Monday through Sunday Summary. I put the current date on the form each day.

I need to date the columns each day based on the date I enter.

Date I enter:
11/15/22 Tuesday

I need to return this in each column:
Monday 11/14/22
Tuesday 11/15/22
Wednesday 11/16/22
Thursday 11/17/22
Friday 11/18/22
Saturday 11/19/22
Sunday 11/20/22

I figure the first column is all I need to enter the formula in, then take that cell and add 1 day for the rest of the cells.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
It would help if you showed the actual sheet layout.

Generically:

Enter the start date in the first cell
In the second cell use
Excel Formula:
``=A1+1``
where A1 is the first cell
Copy that formula to the right for the other cells
Select all cells and use custom format
Excel Formula:
``Dddd mm/dd/yy``

Cell Formulas
RangeFormula
B1:F1B1=A1+1

It would help if you showed the actual sheet layout.

Generically:

Enter the start date in the first cell
In the second cell use
Excel Formula:
``=A1+1``
where A1 is the first cell
Copy that formula to the right for the other cells
Select all cells and use custom format
Excel Formula:
``Dddd mm/dd/yy``

Cell Formulas
RangeFormula
B1:F1B1=A1+1

Thanks for the reply. After trail and error I found a good solution since I am going to update the date each day:

=ifs(TEXT((D17),”ddd”)=“Sun”,D17-6,(TEXT((D17),”ddd”)=“Sat”,D17-5,(TEXT((D17),”ddd”)=“Fri”,D17-4,(TEXT((D17),”ddd”)=“Thu”,D17-3,
(TEXT((D17),”ddd”)=“Wed”,D17-2,(TEXT((D17),”ddd”)=“Tue”,D17-1,(TEXT((D17),”ddd”)=“Mon”,D17,

It's not clear what you want to do. But the formula you showed here is rather convoluted. I suggest you do it more concisely using the WEEKDAY function:
Excel Formula:
``=D17-WEEKDAY(D17,3)``

It's not clear what you want to do. But the formula you showed here is rather convoluted. I suggest you do it more concisely using the WEEKDAY function:
Excel Formula:
``=D17-WEEKDAY(D17,3)``
Thanks for your help in this solution. Worked very well with only a short code.

Replies
5
Views
224
Replies
1
Views
476
Replies
3
Views
224
Replies
1
Views
292
Replies
5
Views
385

1,220,950
Messages
6,157,028
Members
451,392
Latest member
malcv

### 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.

### Which adblocker are you using?

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