Leading the way in Microsoft Office Development

Please contact us if you have any useful formulas we can add to this page.

To make a contribution to this website please e-mail us. feedback3@bettersolutions.com

1) Calculate the number of days in a particular month

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1,-1) = 31

=DAY(DATE(YEAR(B1),MONTH(B1)+1,1,-1) = 29

=DAY(DATE(YEAR(C1),MONTH(C1)+1,1,-1) = 28

=DAY(DATE(YEAR(D1),MONTH(D1)+1,1,-1) = 30

=DAY(DATE(YEAR(E1),MONTH(E1)+1,1,-1) = 30

=DAY(DATE(YEAR(F1),MONTH(F1)+1,1,-1) = 30

=DAY(DATE(YEAR(G1),MONTH(G1)+1,1,-1) = 30

2) Calculate the number of days between 2 dates

=DATEDIF(A1,B1,"D") = 27

=DATEDIF("01/01/2003","28/01/2003","D") = 27

="01/08/03"-"28/08/03" = 27

=B1-A1 = 27

3) Calculate the number of months between 2 dates

=DATEDIF(A1,B1"M") = 7

=DATEDIF("01/01/2003","28/04/2003","M") = 7

4) Calculate the time elapsed between 2 times

=MOD(A1-B1,1) = 21:36

=MOD(TIME(21,36,0)-TIME(3,0,0),1) = 21:36

5) Calculate the number of days that have passed in the current year

=A1-DATE(YEAR(A1),1,0) = 2

=B1-DATE(YEAR(B1),1,0) = 153

="02/01/2004"-DATE(YEAR("01/01/2004"),1,0) = 2

=DATE(2004,1,2)-DATE(2004,1,0) = 2

6) Calculate the number of days left in the current year

=DATE(YEAR(A1),12,31)-A1 = 122

=DATE(YEAR(B1),12,31)-NOW() = 134

=DATE(YEAR("19/08/2005"),12,31)-NOW() = 133

=DATE(YEAR(NOW()),12,31)-NOW() = 134

7) Calculate the corresponding number for a given month

=CHOOSE(MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),1,2,3,4,5,6,7,8,9,10,11,12) = 1

=CHOOSE(MATCH(LEFT(B1,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),1,2,3,4,5,6,7,8,9,10,11,12) = 11

8) Calculate a person's age in years

=INT(YEARFRAC(TODAY(),A1,1)) = 27

=INT(YEARFRAC(TODAY(),B1,1)) = 21

=INT(YEARFRAC(TODAY(),"19/09/1958",1)) = 45

9) Displaying the current time

=A1-B1=10:52:37

=NOW()-TODAY() = 10:52:37

10) Converting a Date String to a Date Value

If you have the date entered as a string (e.g. "20050701" in the format "yyyymmdd") this formula returns the corresponding date serial number.

=DATEVALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&(LEFT(A1,4))) = 38534

=DATEVALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&(LEFT(A1,4))) = 01 July 2005

=DATEVALUE(RIGHT(B1,2)&"/"&MID(B1,5,2)&"/"&(LEFT(B1,4))) = 38338

=DATEVALUE(RIGHT(B1,2)&"/"&MID(B1,5,2)&"/"&(LEFT(B1,4))) = 13 December 2004

11) Displaying a date in a particular format

=TEXT(A1,"dd mmmm, yy") = 01 January, 73

=TEXT(B1,"dd mmmm yy") = 16-Aug 02

=TEXT(TODAY(),"dd mmmm, yy") = 19 August, 05

=TEXT("01/07/2005","dd mmmm, yy") = 01 July, 05

Calculate the number of days left in the month