subtracting 2 dates

[bzl]

hi, I know you can subtract 2 dates e.g. cell A1 has 12/3/2015 and cell A2 has 20/3/2015 by using =A2-A1, which gives you 8 days or by using =datedif(A1,A2,"d")....but can you use a cell reference which has a date and subtract an actual date e.g. =A1-01/03/2015 or is this a no no...it's just that I wanted to quickly test some date differences without putting the second date in a cell...

[benishiryo]

hi bzl. sure. but you have to use double quotes or a formula:
=A1-"1mar2015"
format to general
OR
=A1-DATE(2015,3,1)

i always spell out the month instead of using numbers because different region settings affect the results. d/m/y or m/d/y

[TMS]

=A1-DATEVALUE("01/03/2015")

Regards, TMS

[bzl]

Hi, thank you benishiryo and TMS, they were great options...

[Rob Kennedy]

Or

=A1-VALUE("12/03/2015")

but bear in mind when both the day and the month are 12 or less, dependent on your Excel settings this may be interpreted differently for different regions - 12th March or 3rd of December.

[bzl]

thanks Rob, will keep that in mind...

[TMS]

You're welcome.