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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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)))
 
Upvote 0
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"))
 
Upvote 0
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 #?
 
Upvote 0
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.
 
Upvote 0
Sorry! I think i'm beginning to figure it out the original unsimplified version seems to have helped w/ one of my questions!!! Thanks.
 
Upvote 0
An alternate formula giving the same results is:

=CHOOSE(WEEKDAY(A1),"Friday","Friday","Monday","Monday","Wednesday","Wednesday","Wednesday")
 
Upvote 0
A shorter formula giving the same result as that:

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

Forum statistics

Threads
1,222,383
Messages
6,165,665
Members
451,983
Latest member
Raph24

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
Back
Top