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

[Fa.Fa]

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.

 

[buxtongt]

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]

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

=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]

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]

Answer posted

 

[Fa.Fa]

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]

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]

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]

An alternate formula giving the same results is:

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

 

[Scott Huish]

A shorter formula giving the same result as that:

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