Excel 2010 - date calculation

User avatar
John Gray
PlatinumLounger
Posts: 5401
Joined: 24 Jan 2010, 08:33
Location: A cathedral city in England

Excel 2010 - date calculation

Post by John Gray »

Is there a straightforward Excel formula to determine the 1st April date preceding any arbitrary date, in dd/mm/yyyy format? IF-free, if possible!

For example, for both 02/08/2020 and 27/03/2021 I want to generate the date 01/04/2020.

Thanks!
John Gray

Venison is quiet deer, and quite dear.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2010 - date calculation

Post by HansV »

Let's say the date is in A1.

=DATE(YEAR(A1)-(MONTH(A1)<4),4,1)
Best wishes,
Hans

User avatar
John Gray
PlatinumLounger
Posts: 5401
Joined: 24 Jan 2010, 08:33
Location: A cathedral city in England

Re: Excel 2010 - date calculation

Post by John Gray »

Excellent!
John Gray

Venison is quiet deer, and quite dear.