hi all..
how to determine due date based on criteria maximum allowed days, working days and holidays days
then filled color using Conditional Formatting based on system date on Google System
this my link share
https://docs.google.com/spreadsheets...it?usp=sharing
anyone help me, greatly appreciated..
john m
maybe this make more clear
please, check into my link file
Determine working day intl due date in col E2 down with add max days (D2 down), exclude Saturday and Sunday with holidays list in col I6 till I7
after due date is found in Cell E2 so highligted color in cell F2 down (desired result2)
with 3 color (grey, yellow and red) with criteria ;
- for example if due date (in cell E2) is 1/11/2022 using 3 rules :
in cell F2 substract 3 days, showing new due date and highligted with Grey Color
or if cell F2 substract 7 days, showing NEW DUE DATE and highligted with Yellow color
or if cell F2 substract 9 days, showing New Due Date and highligted with Red color
New Due Date' in Col. F2 down shown based on automatic system date in Google Date System
Not sure how this translates to google, however this is how it might be done in Excel.
The dates in column E are populated using: =WORKDAY.INTL(C2,D2,1,I$6:I$7)
The dates in column F are populated using: =SUM(C2,D2)
The conditional formatting for column F is as follows*:
Grey: =E2-F2<=3
Yellow: =E2-F2<=7
Red: =E2-F2<=9
*Note that the rules should be in the order listed above.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
hi JeteMc....thank you but not full work
1. i want to formula work in Google Sheet, how to modified/adjustable..?
2. in Col F (New date) i want to Automatic Show Date based on date system in device for example like function today(), how to tomorrow, next day ..etc....
3. for example in New Due Date today is Yellow (CF), how to automatic show Red in tomorrow or next day etc..
the core is the coloring is dynamic based on criteria above..
According to this article the WORKDAY.INTL function for google is the same as for Excel: https://www.excelhow.net/google-shee...-function.html
It also seems that TODAY() works in google so you can put =TODAY() in cell F2 and copy down.
It seems as if you want the dates in column F to highlight yellow if they are within three days of the due date and then red if the due date is past, so I am guessing that they should highlight grey if they are more than 3 days off.
If so then the following will work in Excel and I assume in google too.
Red: =E2<F2
Yellow: =E2<=F2+3
Grey: =E2>F2+3
Rules need to be in the order listed above.
Let us know if you have any questions.
hi..JeteMc, thank you very much!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
hiJeteMc, ..
why different from result between using Excel and Google Sheet?
Note:
1. Calculate exclude Saturday and Sunday
2. holidays list is always not contains Saturday and Sunday
Which correct it?
As to #1, according to the article linked in the first sentence of post #5 there should not be any difference in the way WORKDAY.INTL calculates in google and Excel.
As to #2, there should be no reason to list Saturdays and Sundays in the holiday list as they should already be omitted from the WORKDAY.INTL calculation.
I don't have google so I can't test.
Maybe another contributor could troubleshoot if you can post a link to the spreadsheet that is not calculating correctly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks