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!
Excel 2010 - date calculation
-
- PlatinumLounger
- Posts: 5401
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England
Excel 2010 - date calculation
John Gray
Venison is quiet deer, and quite dear.
Venison is quiet deer, and quite dear.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2010 - date calculation
Let's say the date is in A1.
=DATE(YEAR(A1)-(MONTH(A1)<4),4,1)
=DATE(YEAR(A1)-(MONTH(A1)<4),4,1)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 5401
- Joined: 24 Jan 2010, 08:33
- Location: A cathedral city in England