| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date to show then end of the week
Hi
I am trying to carryout some calcualtions on dates to show the day before the next working day. Is there a formula I can use so for example if the date is Friday 3 January 2009 it will show Sunday 5 January - or alternativly if easter weekend the last working day would be Thursday 8 April 2009 so the date shown would be Monday 12 April 2009? Many thanks in advance for your help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date to show then end of the week
If A1 contains a date, then:
=A1+CHOOSE(WEEKDAY(A1),0,6,5,4,3,2,1) will give you the date of the upcoming Sunday If A1 contains a Sunday, the fomula will return A1 If the Monday following the Sunday is a holiday, then just add one, etc. -- Gary''s Student - gsnu200830 "Pendelfin" wrote: Hi I am trying to carryout some calcualtions on dates to show the day before the next working day. Is there a formula I can use so for example if the date is Friday 3 January 2009 it will show Sunday 5 January - or alternativly if easter weekend the last working day would be Thursday 8 April 2009 so the date shown would be Monday 12 April 2009? Many thanks in advance for your help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date to show then end of the week
Thanks Gary.
By changing the "+" to a "-", I was able to use your formula to find the "week commencing" date for any date entered in A1. "Gary''s Student" wrote: If A1 contains a date, then: =A1+CHOOSE(WEEKDAY(A1),0,6,5,4,3,2,1) will give you the date of the upcoming Sunday If A1 contains a Sunday, the fomula will return A1 If the Monday following the Sunday is a holiday, then just add one, etc. -- Gary''s Student - gsnu200830 "Pendelfin" wrote: Hi I am trying to carryout some calcualtions on dates to show the day before the next working day. Is there a formula I can use so for example if the date is Friday 3 January 2009 it will show Sunday 5 January - or alternativly if easter weekend the last working day would be Thursday 8 April 2009 so the date shown would be Monday 12 April 2009? Many thanks in advance for your help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to show the day of the week | Excel Discussion (Misc queries) | |||
| Display "this week" column headers w/date & day of week? | Excel Worksheet Functions | |||
| How to format cells to show dates as the week-ending date of that | Excel Worksheet Functions | |||
| How do I get a cell to show the day of the week when date entered | Excel Discussion (Misc queries) | |||
| I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) | |||
Linear Mode
