Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekending Function
Hello
I have a worksheet with 15000 lines and we have a date field. I now need to work out the weekending date (Fridays) so if the date is 07 Apr 09 the weekending date should read 10 Apr 09 Any help would be appreciated Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekending Function
I've tried the following formula
=7-WEEKDAY(A1)+A1 but it also returns the time 01/01/09 00:00:00 how do I get it to return just the DATE and not the time as I need to pivot on this set of data? Many thanks "Tracey" wrote: Hello I have a worksheet with 15000 lines and we have a date field. I now need to work out the weekending date (Fridays) so if the date is 07 Apr 09 the weekending date should read 10 Apr 09 Any help would be appreciated Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekending Function
It looks as if you have got just the date, as the time is showing as zero.
Just choose an appropriate format for the cell. If A1 is non-integer you could change =7-WEEKDAY(A1)+A1 to =7-WEEKDAY(A1)+INT(A1) or =INT(7-WEEKDAY(A1)+A1) -- David Biddulph "Tracey" wrote in message ... I've tried the following formula =7-WEEKDAY(A1)+A1 but it also returns the time 01/01/09 00:00:00 how do I get it to return just the DATE and not the time as I need to pivot on this set of data? Many thanks "Tracey" wrote: Hello I have a worksheet with 15000 lines and we have a date field. I now need to work out the weekending date (Fridays) so if the date is 07 Apr 09 the weekending date should read 10 Apr 09 Any help would be appreciated Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekending Function
Hi,
Try =A1-WEEKDAY(A1+1)+7 If it returns the time then change the format to date Mike "Tracey" wrote: I've tried the following formula =7-WEEKDAY(A1)+A1 but it also returns the time 01/01/09 00:00:00 how do I get it to return just the DATE and not the time as I need to pivot on this set of data? Many thanks "Tracey" wrote: Hello I have a worksheet with 15000 lines and we have a date field. I now need to work out the weekending date (Fridays) so if the date is 07 Apr 09 the weekending date should read 10 Apr 09 Any help would be appreciated Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekending Function
=A1+(5-WEEKDAY(A1,2))
and format the result cell like "dd mm yy" Regards, Stefi €˛Tracey€¯ ezt Ć*rta: I've tried the following formula =7-WEEKDAY(A1)+A1 but it also returns the time 01/01/09 00:00:00 how do I get it to return just the DATE and not the time as I need to pivot on this set of data? Many thanks "Tracey" wrote: Hello I have a worksheet with 15000 lines and we have a date field. I now need to work out the weekending date (Fridays) so if the date is 07 Apr 09 the weekending date should read 10 Apr 09 Any help would be appreciated Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekending Function
Thank you!
That is exactly what was needed! Greatly Appreciated! "David Biddulph" wrote: It looks as if you have got just the date, as the time is showing as zero. Just choose an appropriate format for the cell. If A1 is non-integer you could change =7-WEEKDAY(A1)+A1 to =7-WEEKDAY(A1)+INT(A1) or =INT(7-WEEKDAY(A1)+A1) -- David Biddulph "Tracey" wrote in message ... I've tried the following formula =7-WEEKDAY(A1)+A1 but it also returns the time 01/01/09 00:00:00 how do I get it to return just the DATE and not the time as I need to pivot on this set of data? Many thanks "Tracey" wrote: Hello I have a worksheet with 15000 lines and we have a date field. I now need to work out the weekending date (Fridays) so if the date is 07 Apr 09 the weekending date should read 10 Apr 09 Any help would be appreciated Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekending Saturday | Excel Worksheet Functions | |||
Weekending date issue | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Summing weekending dates | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |