Date Format used in a formula.

itsgrady

Board Regular
Joined
Sep 11, 2022
Messages
56
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,660
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 
Upvote 0

itsgrady

Board Regular
Joined
Sep 11, 2022
Messages
56
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
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,
 
Upvote 0

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,660
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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)
 
Upvote 0
Solution

itsgrady

Board Regular
Joined
Sep 11, 2022
Messages
56
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
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. :)
 
Upvote 0

Forum statistics

Threads
1,186,245
Messages
5,956,810
Members
438,267
Latest member
sujan356

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
Top