Hello,
I am trying to interrogate a spreadsheet to return a month number if a date falls between the first and start of a month. I understand this would be easy using MONTH function but as my data covers several years I want to allocate a month and year number.
I have the IF function for a single entry which works correctly
=IF(AND(B4>=DATEVALUE("01/05/2020"),B4<=DATEVALUE("31/05/2020")),"5/20","NO")
but when I try to convert to IF to an IFS function by adding in other months then I cannot get it to work.
=IFS(AND(B5>=DATEVALUE("01/05/2020"),B5<=DATEVALUE("31/05/2020")),"5/20",B5>=DATEVALUE("01/04/2020"),B5<=DATEVALUE("30/04/2020"),"4/20",B5>=DATEVALUE("01/03/2020"),B5<=DATEVALUE("31/03/2020"),"3/20")
returns me a correct response for May, a TRUE value for an April date and #VALUE for March
Could anyone either
1. tell me where I am going wrong, or
2. show me an easier way?
Thanks in advance
I am trying to interrogate a spreadsheet to return a month number if a date falls between the first and start of a month. I understand this would be easy using MONTH function but as my data covers several years I want to allocate a month and year number.
I have the IF function for a single entry which works correctly
=IF(AND(B4>=DATEVALUE("01/05/2020"),B4<=DATEVALUE("31/05/2020")),"5/20","NO")
but when I try to convert to IF to an IFS function by adding in other months then I cannot get it to work.
=IFS(AND(B5>=DATEVALUE("01/05/2020"),B5<=DATEVALUE("31/05/2020")),"5/20",B5>=DATEVALUE("01/04/2020"),B5<=DATEVALUE("30/04/2020"),"4/20",B5>=DATEVALUE("01/03/2020"),B5<=DATEVALUE("31/03/2020"),"3/20")
returns me a correct response for May, a TRUE value for an April date and #VALUE for March
Could anyone either
1. tell me where I am going wrong, or
2. show me an easier way?
Thanks in advance