excel生日提醒公式

C2 =IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))=TODAY(),"生日快乐!","距好友生日还有"&DATE(IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(B2),DAY(B2))-TODAY()&"天!")

或

C2 =IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))=TODAY(),"生日快乐!","距好友生日还有"&DATEDIF(TODAY(),DATE(IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(B2),DAY(B2)),"D")&"天!")

公式下拉

A2条件格式公式：

条件1公式 =DATE(IF(DATE(YEAR(TODAY()),MONTH($B2),DAY($B2))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH($B2),DAY($B2))-TODAY()=0

设当天的显示，示例为红底白字

条件2公式 =DATE(IF(DATE(YEAR(TODAY()),MONTH($B2),DAY($B2))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH($B2),DAY($B2))-TODAY()<=3

设1-3天内的显示，示例为粉红色字

条件3公式 =DATE(IF(DATE(YEAR(TODAY()),MONTH($B2),DAY($B2))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH($B2),DAY($B2))-TODAY()<=7

设4-7天内的显示，示例为蓝色字

复制A2，选中A2:C14，右键 - 选择性粘贴 - 格式

设置B2:B14格式，日期

如果生日在a列，姓名在b列

按alt+f11

双击左侧你的工作表名称如：sheet1

复制并粘贴以下代码

private

sub

workbook_open()

dim

i

dim

n

as

string

n

=

"温馨提示，今日有以下员工过生日："

sheets("sheet1")select

for

i

=

1

to

65536

if

range("a"

&

i)value

=

date

then

n

=

n

+

range("b"

&

i)value

+

"；"

end

if

next

i

msgbox

n

end

sub

保存后打开工作表就会提示