locked
How to add calculated column ? RRS feed

  • Question

  • I have a calculated column "FutureDateTime" in a library I am using following formula. It looks like

    8/12/2013 12:00 AM


    =[Today] + (2 - MOD(WEEKDAY([Today]),7)) + 7

    This works but the time is always 12:00 AM. I need this time to be 10:00 AM



    Tuesday, August 6, 2013 4:47 PM

Answers

  • As I understood the FutureDateTime must be Monday and must 10:00 AM. I would following formula which gives you specific future day (in this case Monday) with specific time.

    =(NewToday+(2-MOD(WEEKDAY(NewToday),7))+7)+(1/24*10)

    • Marked as answer by SP_Junkie Friday, August 9, 2013 4:21 AM
    Tuesday, August 6, 2013 6:41 PM
  • Hi,

    Use this following formula, which will record the time as the current time + Next Monday instead of 12:00AM, which is simple and straight forward.

    =NOW()+(2-MOD(WEEKDAY(NOW()),7))+7

    Suppose, you want exact Next Monday 10AM only, use the following formula.  I have tested this formula, working as expected, which is bit complicated

    =(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+(2-MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))),7))+7)+(1/24*10)

    Formula Settings

    Results

    **Please mark as answered and helpful

    Recently Published Gallery, please rate and download
    PowerShell Script for Creating Site Collection with Host Headers

    Wednesday, August 7, 2013 1:10 AM

All replies

  • Hi,

    Use the following formula, which will record the time as the current time when the record is inserted instead of 12:00AM

    =NOW()+(2-MOD(WEEKDAY(NOW()),7))+7

    **Please mark as answered and helpful

    Recently Published Gallery, please rate and download
    PowerShell Script for Creating Site Collection with Host Headers


    Tuesday, August 6, 2013 6:00 PM
  • As I understood the FutureDateTime must be Monday and must 10:00 AM. I would following formula which gives you specific future day (in this case Monday) with specific time.

    =(NewToday+(2-MOD(WEEKDAY(NewToday),7))+7)+(1/24*10)

    • Marked as answer by SP_Junkie Friday, August 9, 2013 4:21 AM
    Tuesday, August 6, 2013 6:41 PM
  • If you don't need a date column as the result, you could do this with a few calculated columns:

    • add a calculated column "NextMonday" to figure out next Monday: =(NOW()+(2-MOD(WEEKDAY(NOW()),7))+7)
    • add a column called "Time10AM" and set it to default value of any date with 10:00 AM as the time element
    • add a calculated column "CalcHours" with a formula =TEXT(Time10AM,"h:mm")
    • add another calculated column for the final result with a formula =TEXT(NextMonday,"mm/dd/yyyy")&" "&CalcHours

    Tom


    SharePoint Systems Officer, Capital Regional District, BC, Canada

    Tuesday, August 6, 2013 7:26 PM
  • Hi,

    Use this following formula, which will record the time as the current time + Next Monday instead of 12:00AM, which is simple and straight forward.

    =NOW()+(2-MOD(WEEKDAY(NOW()),7))+7

    Suppose, you want exact Next Monday 10AM only, use the following formula.  I have tested this formula, working as expected, which is bit complicated

    =(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+(2-MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))),7))+7)+(1/24*10)

    Formula Settings

    Results

    **Please mark as answered and helpful

    Recently Published Gallery, please rate and download
    PowerShell Script for Creating Site Collection with Host Headers

    Wednesday, August 7, 2013 1:10 AM