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

Fa.Fa

New Member
Joined
Oct 12, 2011
Messages
5
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.

Please help me out as much as you can't. I couldn't find it on google or youtube. i probably don't know what exactly to look for.

thanks in advance.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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.
 

Jose_Chasez

Board Regular
Joined
May 25, 2009
Messages
117
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)))
 

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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"))
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
Answer posted
 
Last edited:

Fa.Fa

New Member
Joined
Oct 12, 2011
Messages
5
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 #?
 

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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.
 

Fa.Fa

New Member
Joined
Oct 12, 2011
Messages
5
Sorry! I think i'm beginning to figure it out the original unsimplified version seems to have helped w/ one of my questions!!! Thanks.
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
An alternate formula giving the same results is:

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
A shorter formula giving the same result as that:

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

Watch MrExcel Video

Forum statistics

Threads
1,099,548
Messages
5,469,366
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top