Log In
Register
Home
Active Member
March 18, 2020
Offline
Hi Phil/everyone,
I would like to ask what are the specific excel formula since I am having hard time creating it, I could say the if and else statement but can't properly able to work it out in the excel sheet.
So here's the scenario:
I want to know the number of '#' 'Name' in a specific date like for example Jan 1/1/2020 - 1/30/2020.
Based on my spreadsheet, I want to know the January total from a specific person/name and feb and so forth.
more a programming approach:
if 'Ralph' (1/1/2020 - 1/30/2020) display January total
I hope I was able to explain it properly here.
See Answer
Trusted Members
December 20, 2019
Offline
I think you want the EOMONTH function. Mynda wrote a blog on it a while ago https://www.myonlinetraininghu.....h-function
Bascially EOMONTH give you the end of the month, so the last day of Jan would be EOMONTH(DATE(2020,1,1),0) and to get the first day you would need to do EOMONTH(DATE(2020,1,1),-1)+1 (the -1 takes us back to the 31st of December and the +1 makes it the 1st of Jan.
You then need to add in the greater than and less thans with a countif to get =COUNTIFS($A:$A,">="&EOMONTH(DATE(2020,1,1),-1)+1,$A:$A,"<="&EOMONTH(DATE(2020,1,1),0),$B:$B,$H8)
I dont like putting the date in this formula so i would put the 1/1/2020 in a cell and reference it as per the top example in the attched workbook.
PS i had to mess around with you dates to get them to work on a UK machine
Purfleet
Answers Post
1 Guest(s)
