Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count Days excluding Sundays
Hi All,
I would like to put a date in one cell, while in the other, show the date that is 30 days earlier -- but not counting Sundays. In other words, I have to have something at this destination on date B, then I must mail it on Date A... Help?! |
#2
|
|||
|
|||
Hi
if you put your data in A1 try: =A1-SMALL(IF((WEEKDAY(A1-(ROW(INDIRECT("1:"&30*10))),2)<7),ROW(INDIRECT ("1:"&30*10))),30) -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... Hi All, I would like to put a date in one cell, while in the other, show the date that is 30 days earlier -- but not counting Sundays. In other words, I have to have something at this destination on date B, then I must mail it on Date A... Help?! |
#3
|
|||
|
|||
Hi
forgot to mention that you have to enter this as array formula with CTRL+SHIFT+ENTER and that it does not take care of holidays -- Regards Frank Kabel Frankfurt, Germany "Frank Kabel" schrieb im Newsbeitrag ... Hi if you put your data in A1 try: =A1-SMALL(IF((WEEKDAY(A1-(ROW(INDIRECT("1:"&30*10))),2)<7),ROW(INDIRECT ("1:"&30*10))),30) -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... Hi All, I would like to put a date in one cell, while in the other, show the date that is 30 days earlier -- but not counting Sundays. In other words, I have to have something at this destination on date B, then I must mail it on Date A... Help?! |
#4
|
|||
|
|||
BRILLIANT! Thanks.
-----Original Message----- Hi forgot to mention that you have to enter this as array formula with CTRL+SHIFT+ENTER and that it does not take care of holidays -- Regards Frank Kabel Frankfurt, Germany "Frank Kabel" schrieb im Newsbeitrag ... Hi if you put your data in A1 try: =A1-SMALL(IF((WEEKDAY(A1-(ROW(INDIRECT("1:"&30*10))),2) <7),ROW(INDIRECT ("1:"&30*10))),30) -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... Hi All, I would like to put a date in one cell, while in the other, show the date that is 30 days earlier -- but not counting Sundays. In other words, I have to have something at this destination on date B, then I must it on Date A... Help?! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Production/Man Days in Excel | Excel Discussion (Misc queries) | |||
Count If Formula | Excel Worksheet Functions | |||
How can I use count function in excel where I have several criter. | Excel Worksheet Functions | |||
how do I make a function to count days? | Excel Worksheet Functions | |||
count based on two fields - need quickly | Excel Worksheet Functions |