Search code examples
excel-formulaexcel-2007

Get the following Monday with a specific timestamp

I have the following formula that gets the following monday if the date falls on a saturday. However, I need to be able to get the monday with a timestamp of 00:00:00 AM.

The current cell has a timestamp of 4/15/18 18:47:11 PM and the formula returns 4/16/18 18:47:11 PM. I need it to return 4/16/18 00:00:00 AM

=IF(MOD(WEEKDAY(D1),7)=1,D1+1,"")
Solution

  • Throw in a TRUNC.

    =IF(MOD(WEEKDAY(D1),7)=1,TRUNC(D1+1,0),"")