Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Formatting combined data

Copper Contributor

Is it possible to format a spreadsheet with multiple data in the cells? I need to create a yearly spreadsheet with the dates written as (example): Monday-02/06/2024. Due to the nature of the spreadsheet, it needs to appear exactly like that. I don't see that listed in the choices for Date Formatting. If its not possible, I will need to copy, paste and edit each cell individually which is kind of tedious. I was hoping there might be a way to conditionally format the whole year in columns. Thank you.

21 Replies
best response confirmed by Birdiedahlby11 (Copper Contributor)
Solution

@Birdiedahlby11 Select the cells with the dates.

Select 'More Number Formats...' from the 'Number Format' dropdown on the Home tab of the ribbon.

Select Custom in the Category list.

Enter the following in the Type box, then click OK.

 

dddd-mm/dd/yyyy

 

or if you use European d/m/y format:

 

dddd-dd/mm/yyyy

@Birdiedahlby11 

 

Custom Format Cell: dddd-dd/mm/yyyy

Sample.png

Thank you so much for your help.

 

@L z. 

Thank you so much!!
Quick question: I applied the formatting as listed above but when I try to replicate it to adjoining cells, it just copies the data from original cell instead of formatting it in the requested order; ie Monday, 04/01/2024 (repeats this to additional columns). I don't know if its possible but was hoping I could get a formula where it would automatically go: Monday, 04/01/2024, Tuesday 04/02/2024, Wednesday 04/03/2024 etc. Does that make sense? I am new at using Excel so I really appreciate your help. Thank you.

@Birdiedahlby11 

 

No formula required. With your 1st formatted date in A1 below:

Sample.png

- Move your mouse to the bottom right corner of cell A1 until you see a cross (+)

- Right-click and Hold the click

- Drag it to B1, C1, D1....

 

Thank you. I tried that but it just copies the data and adds a year to it (Monday-04/01/24, Monday-04/01/2025, Monday-04/01/2026). I have been googling help but not sure what to do. I tried putting a sequence but that doesn't work either. Maybe its because I am using only one column for my date information and trying to create a formula or sequence in the column next to it??
Quite strange as this works no problem here...
You want to create a sequence, OK but what version of Excel do you run (formatting sequenced cells will be required)?
I am sorry but I don't understand your reply. Did you attach something?
Just tell me what version of Excel you have/run
Excel 365 I think.

@Birdiedahlby11 

 

Assume you want to generate all dates in 2024, so 365 dates, in 1 row:

=DATE(2024,1,SEQUENCE(,365))

in 1 column:

=DATE(2024,1,SEQUENCE(365))

then select the generated cells and apply them the Custom Date Format discussed above

Thank you. I tried that but it didn't work. Here is what I am trying to achieve on a spreadsheet. I am thinking it's not possible because I have the days of the week attached to the month/day/year. I am using a spreadsheet that was created by another department and this is how they want it. I have to add dates for the new year; its an educational setting and the worksheet is used to track attendance.

Thursday-02/29/2024 Friday-03/01/2024 Monday-03/04/2024
Sorry but I don't understand why this doesn't work and what 'I have the days of the week attached to the month/day/year' actually means
Could you please post your workbook, or if not yet allowed here upload & share it with OneDrive, Google Drive or any other file sharing service?
No sorry. Thank you anyway.

@Birdiedahlby11 

 

Creating a sample workbook reflecting your setup shouldn't take more than 5 minutes

Up to you though...

@L z. 

This is what I am trying to do across the top of worksheet. I had to type these individually because I can't get the series function to work.

Tuesday-01/02/2024Wednesday-01/03/2024Thursday-01/04/2024Friday-01/05/2024Monday-01/08/2024
     
     
     
     
     
     
     
     
     
     
     
     
     
     

@Birdiedahlby11 

See the attached demo workbook. There is a WORKDAY formula in B1, and this has been copied to the right.

When I put the formula you showed me in my worksheet I get an error message. (see below).
I have data in the first five columns of the worksheet so put my first date in column F, then typed the formula you showed in your worksheet in column G.

Tuesday/01/02/2024 #VALUE!
1 best response

Accepted Solutions
best response confirmed by Birdiedahlby11 (Copper Contributor)
Solution

@Birdiedahlby11 Select the cells with the dates.

Select 'More Number Formats...' from the 'Number Format' dropdown on the Home tab of the ribbon.

Select Custom in the Category list.

Enter the following in the Type box, then click OK.

 

dddd-mm/dd/yyyy

 

or if you use European d/m/y format:

 

dddd-dd/mm/yyyy

View solution in original post