Amend IF(WEEKDAY) formula to avoid negative dates error

dunlopoil

Board Regular
Joined
May 29, 2008
Messages
92
Hi,
I'm using the formula =G2-(IF(WEEKDAY(G2)>1,WEEKDAY(G2)-2,6)) to convert a date to the first day of that week (Monday), kindly suggested by Schwarzmanne.

I want to amend this to avoid the 'negative dates or times are displayed as ####' error that appears if any cells are blank.

Anyone help?

Thanks,
Doug.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
To avoid #### if G2 is blank you can use Richard's formula and format cell with formula as

dd/mm/yy;;

or amend to

=IF(G2="","",G2-WEEKDAY(G2,3))
 

Watch MrExcel Video

Forum statistics

Threads
1,100,205
Messages
5,473,152
Members
406,849
Latest member
AndreasMs

This Week's Hot Topics

Top