Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Hi,
Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Try this:
=INT(A1)-WEEKDAY(A1,3)+7 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Works great thanks.
I did have to change the 7 to 8 though, not sure why. NOW=25-01-08 and result is 27-01 with +7, 28-01 with +8. But this will do fine. "T. Valko" wrote: Try this: =INT(A1)-WEEKDAY(A1,3)+7 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Are you sure, Hendrik? Which version of Excel? It seems to work OK for me
in Excel 2003. What number (in General format) is NOW() returning? What does =WEEKDAY(A1,3) return? [I could get your wrong answer by using WEEKDAY(A1,2) instead of WEEKDAY(A1,3) but I hope you copied and pasted T Valko's formula rather than retyping it and risking errors.] -- David Biddulph "Hendrik" wrote in message ... Works great thanks. I did have to change the 7 to 8 though, not sure why. NOW=25-01-08 and result is 27-01 with +7, 28-01 with +8. But this will do fine. "T. Valko" wrote: Try this: =INT(A1)-WEEKDAY(A1,3)+7 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Hmmm....
Well, it works for me as: =INT(A1)-WEEKDAY(A1,3)+7 Retrns 1/28/2008 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Works great thanks. I did have to change the 7 to 8 though, not sure why. NOW=25-01-08 and result is 27-01 with +7, 28-01 with +8. But this will do fine. "T. Valko" wrote: Try this: =INT(A1)-WEEKDAY(A1,3)+7 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Hi,
I made a mistake. Sorry guys, works perfect! -- Hendrik Kleine "Hendrik" wrote: Works great thanks. I did have to change the 7 to 8 though, not sure why. NOW=25-01-08 and result is 27-01 with +7, 28-01 with +8. But this will do fine. "T. Valko" wrote: Try this: =INT(A1)-WEEKDAY(A1,3)+7 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate next Monday (date) based on NOW
Glad you got it straightened out. Thanks for letting us know.
-- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, I made a mistake. Sorry guys, works perfect! -- Hendrik Kleine "Hendrik" wrote: Works great thanks. I did have to change the 7 to 8 though, not sure why. NOW=25-01-08 and result is 27-01 with +7, 28-01 with +8. But this will do fine. "T. Valko" wrote: Try this: =INT(A1)-WEEKDAY(A1,3)+7 -- Biff Microsoft Excel MVP "Hendrik" wrote in message ... Hi, Say I have a formula in A1: "=NOW()" In another cell I want to calculate the next Monday, but I can't figure this one out. Thanks for any suggestions.. Hendrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate every Monday | Excel Discussion (Misc queries) | |||
Date calculation for Monday of one month to the Monday of the next | Excel Discussion (Misc queries) | |||
Calculate a date based on a weeknumber | Excel Worksheet Functions | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
Formula to Indicate name of day (Sunday, Monday etc..) based on a Date | Excel Discussion (Misc queries) |