| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula
I have a date in column V5 and need to calculate 6 weeks from this date to
prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
=V5+44-WEEKDAY(V5)
"Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
Hi Clare.
You need to use the weekday formula to calculate the day of the week and then use the if function to see if it falls in the later or earlier part of the week. then add or subtract it from six weeks if its the earlier part of the week to take it back to the last monday or from 7 weeks to take it to the next monday if its in the later part of the week. try this : =IF(WEEKDAY(v5,1)<4,v5+41-(WEEKDAY(v5,1)),v5+48-(WEEKDAY(v5,1))) hope its Ok thanks John "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
Sorry Clare
Think I got my 7 times tables wrong or something! I think formula should read: =IF(WEEKDAY(B5,1)<4,B5+43-(WEEKDAY(B5,1)),B5+50-(WEEKDAY(B5,1))) all the best John "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
I of course mean V5 was using B5 in my example:
=IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),B5+50-(WEEKDAY(V5,1))) This assumes that you want mon tue and wed dates to be booked for the monday prior and thur fri sat and sun to fall to the mon following. If you wish thur to fall to the mon prior change the 4 to 5. "Atishoo" wrote: Sorry Clare Think I got my 7 times tables wrong or something! I think formula should read: =IF(WEEKDAY(B5,1)<4,B5+43-(WEEKDAY(B5,1)),B5+50-(WEEKDAY(B5,1))) all the best John "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
try again without me leaving B5 in: sorry
=IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),V5+50-(WEEKDAY(V5,1))) "Atishoo" wrote: I of course mean V5 was using B5 in my example: =IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),B5+50-(WEEKDAY(V5,1))) This assumes that you want mon tue and wed dates to be booked for the monday prior and thur fri sat and sun to fall to the mon following. If you wish thur to fall to the mon prior change the 4 to 5. "Atishoo" wrote: Sorry Clare Think I got my 7 times tables wrong or something! I think formula should read: =IF(WEEKDAY(B5,1)<4,B5+43-(WEEKDAY(B5,1)),B5+50-(WEEKDAY(B5,1))) all the best John "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
Thanks Sean that works great
-- Clare "Sean Timmons" wrote: =V5+44-WEEKDAY(V5) "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
Hi Atishoo
Thanks for all your help it worked tried using the other formulas which were great but yours rounded up and down only thing was I had to change the 1's to 2's to make it round up to Monday's rather than Sundays. Thanks again would never have worked out myself. -- Clare "Atishoo" wrote: try again without me leaving B5 in: sorry =IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),V5+50-(WEEKDAY(V5,1))) "Atishoo" wrote: I of course mean V5 was using B5 in my example: =IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),B5+50-(WEEKDAY(V5,1))) This assumes that you want mon tue and wed dates to be booked for the monday prior and thur fri sat and sun to fall to the mon following. If you wish thur to fall to the mon prior change the 4 to 5. "Atishoo" wrote: Sorry Clare Think I got my 7 times tables wrong or something! I think formula should read: =IF(WEEKDAY(B5,1)<4,B5+43-(WEEKDAY(B5,1)),B5+50-(WEEKDAY(B5,1))) all the best John "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula
Welcome, just out of interest which way did you want thursdays to go?
"Clare" wrote: Hi Atishoo Thanks for all your help it worked tried using the other formulas which were great but yours rounded up and down only thing was I had to change the 1's to 2's to make it round up to Monday's rather than Sundays. Thanks again would never have worked out myself. -- Clare "Atishoo" wrote: try again without me leaving B5 in: sorry =IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),V5+50-(WEEKDAY(V5,1))) "Atishoo" wrote: I of course mean V5 was using B5 in my example: =IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),B5+50-(WEEKDAY(V5,1))) This assumes that you want mon tue and wed dates to be booked for the monday prior and thur fri sat and sun to fall to the mon following. If you wish thur to fall to the mon prior change the 4 to 5. "Atishoo" wrote: Sorry Clare Think I got my 7 times tables wrong or something! I think formula should read: =IF(WEEKDAY(B5,1)<4,B5+43-(WEEKDAY(B5,1)),B5+50-(WEEKDAY(B5,1))) all the best John "Clare" wrote: I have a date in column V5 and need to calculate 6 weeks from this date to prompt a review. I was going to use =(V5+42) but I need to make the date round up or down to the nearest Monday. Is there any way of doing this. Thaks -- Clare |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Linear Mode
