# Help A Sistah Out!!! - Formatting Cell --> Day of Week

#### Fa.Fa

##### New Member
SHORT VERSION:
I have a column for date of draft depending on date of load. I want to be able to have the DRAFT date automatically filed after entering the date

LONG VERSION:

So I started helping out at a fuel company and I was bragging about being good at excel. I didn't know nearly as much as I thought I did! They asked me to do what i thought was a simple task and i'm STUCK!

1. record fuel purchased
2. record fuel sold
keep track of how much is used
=SUM(D6,E7,-F7)- that's the formula i came up w/ that seems to work fine.

But the next part is what is giving me problems.
i was asked to also record a drafting schedule -
EX.
Loads pulled on Tuesday and Wednesday of the prior week will be drafted on Monday
Loads pulled on Thursday, Friday, and Saturday of the prior week will be drafted on Wednesday.
Loads pulled on Sunday and Monday will be drafted on Friday.

I wanted to somehow use the date to automatically give the draft day. and if that wasn't possible i delegated 1-7 to Mon - Sun respectfully. I tried to format cell so they would change the load day of 1 to Monday and in another column under draft day to 5 - Friday for a fuel load on monday to be drafted on friday.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
The weekday function will return a value of 1-7 for Sunday-Saturday. You could use this number in some nested if statements to display the correct day. Something like:

=IF(WEEKDAY(A1)=3,"Monday",IF(WEEKDAY(A1)=4,"Monday",IF(WEEKDAY(A1)=5,"Wednesday",IF(WEEKDAY(A1)=6,"Wednesday",IF(WEEKDAY(A1)=7,"Wednesday","Friday")))))

assuming that your date is in cell a1. There is probably a more elegant way to do this with arrays, but this will work.

This also seems to work. It assumes your Load Date is in cell A2:

Code:
``=IF(OR(WEEKDAY(A2)=3,WEEKDAY(A2)=5),A2+6,IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=4,WEEKDAY(A2)=6),A2+5,IF(OR(WEEKDAY(A2)=2,WEEKDAY(A2)=7),A2+4)))``

Good call on the or statements Jose, that shortens things nicely. This will work too:

=IF(OR(WEEKDAY(A1)=3,WEEKDAY(A1)=4),"Monday",IF(OR(WEEKDAY(A1)=5,WEEKDAY(A1)=6,WEEKDAY(A1)=7),"Wednesday","Friday"))

Last edited:
Thanks so much guys for the quick response. I'm still working out some kinks because unfortunatlely I haven't had to use excel for years!

I have a few f/u questions.
1. I understand we delegated the days of the week to 1-7
- Should I keep a separate column for Load Date 1-7 and Draft Date 1-7
... This seem works fine - Except that 3 & 4 for Tuesday and Wednesdays gives me 9 which essentially means Monday - I'll just fool around w/ the math.

2. My date column was originally mm/dd/yyyy. If i change it to MONDAY mm/dd/yyyy - do you guys think there would be a way I can eliminate having to delegate a # to the weekday? Creating an automatically filled draft day of Monday, Wednesday, or Tuesday?

3. If the only option is using 1-7 is there a way to have the cell show up w/ the corresponding weekday instead of the #?

1. You shouldn't have to keep columns for the 1-7 stuff at all, you can always pull it off of the date format with weekday(a1)

2. It would be possible to do the same thing as the formulas did above if you had a day instead of a date, but unless you want to change it for another reason there is no good reason to do it. The formulas will be similar either way.

3. Either of the formulas I posted will display the day of the week instead of a number. Let me know if they are giving you something different.

Sorry! I think i'm beginning to figure it out the original unsimplified version seems to have helped w/ one of my questions!!! Thanks.

An alternate formula giving the same results is:

=CHOOSE(WEEKDAY(A1),"Friday","Friday","Monday","Monday","Wednesday","Wednesday","Wednesday")

A shorter formula giving the same result as that:

=LOOKUP(WEEKDAY(A1),{1,3,5},{"Friday","Monday","Wednesday"})

Replies
7
Views
403
Replies
7
Views
723
Replies
3
Views
288
Replies
10
Views
472
Replies
3
Views
383

1,206,947
Messages
6,075,798
Members
446,158
Latest member
octagonalowl

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