The Workday formula was working great until D23 where it failed to skip the weekend. It is the same formula for the entire column D, can't figure out why it stopped working.
Thanks in advance for the help!
Dale
The Workday formula was working great until D23 where it failed to skip the weekend. It is the same formula for the entire column D, can't figure out why it stopped working.
Thanks in advance for the help!
Dale
What is wrong? What results are you expecting?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The formula is in every cell of Column D. D23 returns a Saturday date. D2:D22 skipped the weekends as it is supposed to.
Sorry - I realise that now and have changed my questions. Please tell us (a) which results are wrong and (b) what you expect the formula to return.
OK - so what results are you expecting to see in the cells that you asy are not calculating correctly? Don't leave us to work it out ourselves, please - just tell us so that we know what we are working towards. Thanks.
Sorry.
C23 "4/12 10:50 a" + B23 "7" hours = "4/13 8:50 a"
4/13 is a Saturday and should not be counted. D23 should equal "4/15 8:50 a" which is a Monday.
Working hours are 7:00a to 4:00p, Monday through Friday. They are referenced on the 'Info' sheet.
Thanks for the clarification.
Please try at
D2
=IF(B2="","",MOD(C2+MOD(IF(E2>0,E2,B2),9)/24,1)-"9:00"*(MOD(C2+MOD(IF(E2>0,E2,B2),9)/24,1)-"16:00">0)+WORKDAY(C2,(MOD(C2+MOD(IF(E2>0,E2,B2),9)/24,1)-"16:00">0)+INT(IF(E2>0,E2,B2)/9),Info!$A$2:$A$26))
That seems to work on Excel, but this workbook is used on Google sheets. This is the error message I get when using the above formula on Google sheets...
"Function MOD parameter 1 expects number values. But 'ACTUAL' is a text and cannot be coerced to a number."
Nowhere until now did you mention this was not for Excel ...
I have moved the thread to the correct section.
My google sheet work fine.
https://docs.google.com/spreadsheets...it?usp=sharing
2019-04-13 10_05_18-Schedule - Google Sheets.jpg
Thank you Bo!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks