Multiple formulas in same cell

[c.clavin]

Hey guys, new to Excel, new to these forums!

I have a problem writing a formula out for 1 cell.
On 1 hand, I need to make a formula that 1 cell is 2 workdays after another cell (=WORKDAY(D11,2, '*'!B2:B15) but I also have a time requirement, where if it is after 4:30pm it needs to be 3 workdays after the other cell, due to processing issues.

I have a formula, pointing to a different cell in case it is after 4:30 (=IF(NOW()>1630,D11+3)

How do I couple these 2 formulas into one cell so I dont have to make 2 different boxes? Any help would be appreciated. It aggravates me that both work, but I cant incorporate them together. This forum is the best!

 

[T. Valko]

Hey guys, new to Excel, new to these forums!

I have a problem writing a formula out for 1 cell.
On 1 hand, I need to make a formula that 1 cell is 2 workdays after another cell (=WORKDAY(D11,2, '*'!B2:B15) but I also have a time requirement, where if it is after 4:30pm it needs to be 3 workdays after the other cell, due to processing issues.

I have a formula, pointing to a different cell in case it is after 4:30 (=IF(NOW()>1630,D11+3)

How do I couple these 2 formulas into one cell so I dont have to make 2 different boxes? Any help would be appreciated. It aggravates me that both work, but I cant incorporate them together. This forum is the best!

=WORKDAY(D11,2, '*'!B2:B15)

That's an invalid sheet name?

Try something like this...

=WORKDAY(D11,2+(MOD(NOW(),1)>TIME(16,30,0)), Sheet_Name!B2:B15)

Replace Sheet_Name with a valid sheet name!

 

[c.clavin]

Wow that was so fast..and so dead on. Thank you so much.
If I dont want to skip over specific days of the year, do you know how I would add it? like 'dont include the 3 if its july 4th' something like that. Last question. You helped me out so much, thanks again.

 

[T. Valko]

Wow that was so fast..and so dead on. Thank you so much.
If I dont want to skip over specific days of the year, do you know how I would add it? like 'dont include the 3 if its july 4th' something like that. Last question. You helped me out so much, thanks again.

If I understand what you're asking then you would add the date you want to skip to the holidays list.

 

[c.clavin]

It's kind of strange, theres 2 days processing, sometimes if a holiday lands on the first day, you only skip 1 day extra, if its on the 2nd day, you skip two. Probably not possible to create a formula for such things, like specific rules for specific dates.
But I actually had 1 more question, sorry. How would I create a function where if said date is a weekend, it would add 3 days (like after 4:30 on weekdays) I promise I will get out of your hair after this! Thanks again!

 

[T. Valko]

It's kind of strange, theres 2 days processing, sometimes if a holiday lands on the first day, you only skip 1 day extra, if its on the 2nd day, you skip two. Probably not possible to create a formula for such things, like specific rules for specific dates.
But I actually had 1 more question, sorry. How would I create a function where if said date is a weekend, it would add 3 days (like after 4:30 on weekdays) I promise I will get out of your hair after this! Thanks again!

Which date? The date in D11?

 

[c.clavin]

Yes, like if d11=monday then the date would be +2 workdays, but if d11=sat or sun, it would be +3 workdays. Strange system, I know.

 

[T. Valko]

Yes, like if d11=monday then the date would be +2 workdays, but if d11=sat or sun, it would be +3 workdays. Strange system, I know.

Maybe this...

=WORKDAY(D11,2+OR(MOD(NOW(),1)>TIME(16,30,0),WEEKDAY(D11,2)>5), Sheet_Name!B2:B15)

 

[c.clavin]

thanks, that works. How would I add an if statement to that formula? Not used to working with logic in excel. Have a few other statements I need to add, and then it will be flawless.

 

[c.clavin]

Thank you so much for all of your help. You have made my spreadsheet from good to unbelievably good with the few formulas you've given me. I actually bought an Excel how-to book, but it barely scratches the surface of what there is to know.
My question is, in your formula, is there anyway to create exceptions to the rules outlined? If on say March 27th, if instead of skipping 3 days (weekend) if you wanted that particular date to skip say 5 days, is there anyway to write an exception for a few days out of the year? There are oddities that come up, and this would 'unturn' every 'odd' 'stone for the spreadsheet. I really appreciate all of your help, would you recommend any books to me for future learning? Thanks again, and have a great rest of the weekend.