| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formula that will return proper pay day
Thanks for pointing that out, Ron.
"Ron Rosenfeld" wrote: On Wed, 24 Feb 2010 18:43:01 -0800, Matt wrote: JJ - 1.) Enter the dates you want to analyze in column A (ie A1 is 1/15, A2 is 1/31) 2.) Enter the following in cell B1: =IF(WEEKDAY(A2) = 1, A2 - 2, IF(WEEKDAY(A2) = 7, A2 - 1,A2)) 3.) Make sure your cells are formatted as DATE 4.) Copy and paste the formula down In this example, Jan 29 will display in cell B2 because Jan 31 falls on a Sunday --- The formula works as such: Analyzes the date and returns a serial corresponding to the Day of the Week. 1 = Sunday, 2 = Monday, etc. If the serial is 1, then subtract 2 days from the date to get to Friday, if the serial is 7 (for Saturday) then subtract 1 day from the date to get to Friday, otherwise leave the date unchanged. Note that your method does not correct for holidays. --ron . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to return tomorrow's date. | New Users to Excel | |||
| Is there a formula that will return only the month from a date? | Excel Discussion (Misc queries) | |||
| how do i return to normal text from =PROPER(cell) | Excel Discussion (Misc queries) | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Hybrid Mode
