Getting latest year's date from range

PrachiOU

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have a date column with duplicate dates and with same date for multiple years for eg. 01/01/2020 and 01/01/2021 in a range.
I want to get the latest year's date i.e. 01/01/2021 and sum its hourly total.
I tried the following where Dates and Hourly_tot are my named ranges, but its giving me the same value for all the cells.
=INDEX(Hourly_Tot,MATCH(TRUE,TEXT(Dates,"mmyy")=TEXT(Dates,"mmyy"),0))


DayHour of DayHourly Total
1/1/2021​
1:00:00 AM​
0.717​
1/1/2021​
2:00:00 AM​
0.798​
1/1/2021​
3:00:00 AM​
0.866​
1/1/2021​
4:00:00 AM​
0.7​
1/1/2021​
5:00:00 AM​
0.658​
1/2/2021​
1:00:00 AM​
0.829​
1/2/2021​
2:00:00 AM​
0.698​
1/2/2021​
3:00:00 AM​
0.574​
1/2/2021​
4:00:00 AM​
0.612​
1/2/2021​
5:00:00 AM​
0.598​
1/3/2021​
1:00:00 AM​
0.565​
1/3/2021​
2:00:00 AM​
0.505​
1/3/2021​
3:00:00 AM​
0.417​
1/3/2021​
4:00:00 AM​
0.395​
1/3/2021​
5:00:00 AM​
0.556​
1/4/2021​
1:00:00 AM​
0.467​
1/4/2021​
2:00:00 AM​
0.476​
1/4/2021​
3:00:00 AM​
0.546​
1/4/2021​
4:00:00 AM​
0.473​
1/4/2021​
5:00:00 AM​
0.756​
1/5/2021​
1:00:00 AM​
0.684​
1/5/2021​
2:00:00 AM​
0.491​
1/5/2021​
3:00:00 AM​
0.633​
1/5/2021​
4:00:00 AM​
0.485​
1/5/2021​
5:00:00 AM​
0.567​
1/6/2021​
1:00:00 AM​
0.716​
1/6/2021​
2:00:00 AM​
0.547​
1/6/2021​
3:00:00 AM​
0.547​
1/6/2021​
4:00:00 AM​
0.506​
1/6/2021​
5:00:00 AM​
0.502​
1/7/2021​
1:00:00 AM​
0.844​
1/7/2021​
2:00:00 AM​
0.723​
1/7/2021​
3:00:00 AM​
0.627​
1/7/2021​
4:00:00 AM​
0.498​
1/7/2021​
5:00:00 AM​
0.563​
1/8/2021​
1:00:00 AM​
0.74​
1/8/2021​
2:00:00 AM​
0.806​
1/8/2021​
3:00:00 AM​
0.581​
1/8/2021​
4:00:00 AM​
0.682​
1/8/2021​
5:00:00 AM​
0.515​
1/9/2021​
1:00:00 AM​
0.531​
1/9/2021​
2:00:00 AM​
0.553​
1/9/2021​
3:00:00 AM​
0.542​
1/9/2021​
4:00:00 AM​
0.553​
1/9/2021​
5:00:00 AM​
0.547​
1/10/2021​
1:00:00 AM​
0.616​
1/10/2021​
2:00:00 AM​
0.727​
1/10/2021​
3:00:00 AM​
0.551​
1/10/2021​
4:00:00 AM​
0.631​
1/10/2021​
5:00:00 AM​
0.54​
1/11/2021​
1:00:00 AM​
0.631​
1/11/2021​
2:00:00 AM​
0.674​
1/11/2021​
3:00:00 AM​
0.6​
1/11/2021​
4:00:00 AM​
0.565​
1/11/2021​
5:00:00 AM​
0.576​
1/12/2021​
1:00:00 AM​
0.695​
1/12/2021​
2:00:00 AM​
0.636​
1/12/2021​
3:00:00 AM​
0.728​
1/12/2021​
4:00:00 AM​
0.538​
1/12/2021​
5:00:00 AM​
0.698​
1/13/2021​
1:00:00 AM​
0.604​
1/13/2021​
2:00:00 AM​
0.567​
1/13/2021​
3:00:00 AM​
0.474​
1/13/2021​
4:00:00 AM​
0.426​
1/13/2021​
5:00:00 AM​
0.553​
1/14/2021​
1:00:00 AM​
0.63​
1/14/2021​
2:00:00 AM​
0.556​
1/14/2021​
3:00:00 AM​
0.605​
1/14/2021​
4:00:00 AM​
0.502​
1/14/2021​
5:00:00 AM​
0.481​
1/15/2021​
1:00:00 AM​
0.632​
1/15/2021​
2:00:00 AM​
0.558​
1/15/2021​
3:00:00 AM​
0.532​
1/15/2021​
4:00:00 AM​
0.536​
1/15/2021​
5:00:00 AM​
0.382​
1/16/2021​
1:00:00 AM​
1.204​
1/16/2021​
2:00:00 AM​
0.821​
1/16/2021​
3:00:00 AM​
0.735​
1/16/2021​
4:00:00 AM​
0.615​
1/16/2021​
5:00:00 AM​
0.666​
1/17/2021​
1:00:00 AM​
0.574​
1/17/2021​
2:00:00 AM​
0.336​
1/17/2021​
3:00:00 AM​
0.374​
1/17/2021​
4:00:00 AM​
0.479​
1/17/2021​
5:00:00 AM​
0.33​
1/18/2021​
1:00:00 AM​
0.687​
1/18/2021​
2:00:00 AM​
0.557​
1/18/2021​
3:00:00 AM​
0.571​
1/18/2021​
4:00:00 AM​
0.645​
1/18/2021​
5:00:00 AM​
0.519​
1/19/2021​
1:00:00 AM​
0.892​
1/19/2021​
2:00:00 AM​
0.66​
1/19/2021​
3:00:00 AM​
0.722​
1/19/2021​
4:00:00 AM​
0.671​
1/19/2021​
5:00:00 AM​
0.595​
1/20/2021​
1:00:00 AM​
1.33​
1/20/2021​
2:00:00 AM​
0.707​
1/20/2021​
3:00:00 AM​
0.734​
1/20/2021​
4:00:00 AM​
0.756​
1/20/2021​
5:00:00 AM​
0.632​
1/21/2021​
1:00:00 AM​
0.639​
1/21/2021​
2:00:00 AM​
0.701​
1/21/2021​
3:00:00 AM​
0.547​
1/21/2021​
4:00:00 AM​
0.502​
1/21/2021​
5:00:00 AM​
0.579​
1/22/2021​
1:00:00 AM​
0.728​
1/22/2021​
2:00:00 AM​
0.645​
1/22/2021​
3:00:00 AM​
0.591​
1/22/2021​
4:00:00 AM​
0.566​
1/22/2021​
5:00:00 AM​
0.455​
1/23/2021​
1:00:00 AM​
0.948​
1/23/2021​
2:00:00 AM​
0.731​
1/23/2021​
3:00:00 AM​
0.757​
1/23/2021​
4:00:00 AM​
0.72​
1/23/2021​
5:00:00 AM​
0.594​
1/24/2021​
1:00:00 AM​
0.787​
1/24/2021​
2:00:00 AM​
0.94​
1/24/2021​
3:00:00 AM​
0.651​
1/24/2021​
4:00:00 AM​
0.611​
1/24/2021​
5:00:00 AM​
0.655​
1/25/2021​
1:00:00 AM​
0.834​
1/25/2021​
2:00:00 AM​
0.703​
1/25/2021​
3:00:00 AM​
0.706​
1/25/2021​
4:00:00 AM​
0.604​
1/25/2021​
5:00:00 AM​
0.54​
1/26/2021​
1:00:00 AM​
0.814​
1/26/2021​
2:00:00 AM​
0.724​
1/26/2021​
3:00:00 AM​
0.554​
1/26/2021​
4:00:00 AM​
0.678​
1/26/2021​
5:00:00 AM​
0.451​
1/27/2021​
1:00:00 AM​
0.723​
1/27/2021​
2:00:00 AM​
0.49​
1/27/2021​
3:00:00 AM​
0.642​
1/27/2021​
4:00:00 AM​
0.472​
1/27/2021​
5:00:00 AM​
0.533​
1/28/2021​
1:00:00 AM​
0.769​
1/28/2021​
2:00:00 AM​
0.774​
1/28/2021​
3:00:00 AM​
0.624​
1/28/2021​
4:00:00 AM​
0.587​
1/28/2021​
5:00:00 AM​
0.673​
1/29/2021​
1:00:00 AM​
0.737​
1/29/2021​
2:00:00 AM​
0.817​
1/29/2021​
3:00:00 AM​
0.618​
1/29/2021​
4:00:00 AM​
0.717​
1/29/2021​
5:00:00 AM​
0.657​
1/30/2021​
1:00:00 AM​
0.917​
1/30/2021​
2:00:00 AM​
0.678​
1/30/2021​
3:00:00 AM​
0.809​
1/30/2021​
4:00:00 AM​
0.64​
1/30/2021​
5:00:00 AM​
0.678​
1/31/2021​
1:00:00 AM​
0.674​
1/31/2021​
2:00:00 AM​
0.516​
1/31/2021​
3:00:00 AM​
0.626​
1/31/2021​
4:00:00 AM​
0.528​
1/31/2021​
5:00:00 AM​
0.469​
2/1/2021​
1:00:00 AM​
0.499​
2/1/2021​
2:00:00 AM​
0.61​
2/1/2021​
3:00:00 AM​
0.507​
2/1/2021​
4:00:00 AM​
0.606​
2/1/2021​
5:00:00 AM​
0.418​
2/2/2021​
1:00:00 AM​
0.632​
2/2/2021​
2:00:00 AM​
0.642​
2/2/2021​
3:00:00 AM​
0.528​
2/2/2021​
4:00:00 AM​
0.674​
2/2/2021​
5:00:00 AM​
0.503​
2/3/2021​
1:00:00 AM​
0.598​
2/3/2021​
2:00:00 AM​
0.74​
2/3/2021​
3:00:00 AM​
0.541​
2/3/2021​
4:00:00 AM​
0.664​
2/3/2021​
5:00:00 AM​
0.567​
2/4/2021​
1:00:00 AM​
0.712​
2/4/2021​
2:00:00 AM​
0.795​
2/4/2021​
3:00:00 AM​
0.565​
2/4/2021​
4:00:00 AM​
0.658​
2/4/2021​
5:00:00 AM​
0.606​
2/5/2021​
1:00:00 AM​
0.908​
2/5/2021​
2:00:00 AM​
0.862​
2/5/2021​
3:00:00 AM​
0.655​
2/5/2021​
4:00:00 AM​
0.707​
2/5/2021​
5:00:00 AM​
0.614​
1/12/2020​
1:00:00 AM​
0.466​
1/12/2020​
2:00:00 AM​
0.505​
1/12/2020​
3:00:00 AM​
0.511​
1/12/2020​
4:00:00 AM​
0.512​
1/12/2020​
5:00:00 AM​
0.519​
1/13/2020​
1:00:00 AM​
0.603​
1/13/2020​
2:00:00 AM​
0.622​
1/13/2020​
3:00:00 AM​
0.618​
1/13/2020​
4:00:00 AM​
0.61​
1/13/2020​
5:00:00 AM​
0.54​
1/14/2020​
1:00:00 AM​
0.633​
1/14/2020​
2:00:00 AM​
0.694​
1/14/2020​
3:00:00 AM​
0.579​
1/14/2020​
4:00:00 AM​
0.565​
1/14/2020​
5:00:00 AM​
0.572​
1/15/2020​
1:00:00 AM​
0.501​
1/15/2020​
2:00:00 AM​
0.594​
1/15/2020​
3:00:00 AM​
0.47​
1/15/2020​
4:00:00 AM​
0.543​
1/15/2020​
5:00:00 AM​
0.559​
1/16/2020​
1:00:00 AM​
0.689​
1/16/2020​
2:00:00 AM​
0.644​
1/16/2020​
3:00:00 AM​
0.668​
1/16/2020​
4:00:00 AM​
0.594​
1/16/2020​
5:00:00 AM​
0.493​
1/17/2020​
1:00:00 AM​
0.888​
1/17/2020​
2:00:00 AM​
1.022​
1/17/2020​
3:00:00 AM​
0.833​
1/17/2020​
4:00:00 AM​
0.85​
1/17/2020​
5:00:00 AM​
0.745​
1/18/2020​
1:00:00 AM​
0.767​
1/18/2020​
2:00:00 AM​
0.735​
1/18/2020​
3:00:00 AM​
0.685​
1/18/2020​
4:00:00 AM​
0.75​
1/18/2020​
5:00:00 AM​
0.678​
1/19/2020​
1:00:00 AM​
0.754​
1/19/2020​
2:00:00 AM​
0.625​
1/19/2020​
3:00:00 AM​
0.592​
1/19/2020​
4:00:00 AM​
0.648​
1/19/2020​
5:00:00 AM​
0.602​
1/20/2020​
1:00:00 AM​
1.038​
1/20/2020​
2:00:00 AM​
1.004​
1/20/2020​
3:00:00 AM​
0.927​
1/20/2020​
4:00:00 AM​
0.773​
1/20/2020​
5:00:00 AM​
0.839​
1/21/2020​
1:00:00 AM​
0.771​
1/21/2020​
2:00:00 AM​
0.69​
1/21/2020​
3:00:00 AM​
0.668​
1/21/2020​
4:00:00 AM​
0.744​
1/21/2020​
5:00:00 AM​
0.643​
1/22/2020​
1:00:00 AM​
0.717​
1/22/2020​
2:00:00 AM​
0.832​
1/22/2020​
3:00:00 AM​
0.782​
1/22/2020​
4:00:00 AM​
0.749​
1/22/2020​
5:00:00 AM​
0.827​
1/23/2020​
1:00:00 AM​
0.826​
1/23/2020​
2:00:00 AM​
0.762​
1/23/2020​
3:00:00 AM​
0.707​
1/23/2020​
4:00:00 AM​
0.66​
1/23/2020​
5:00:00 AM​
0.648​
1/24/2020​
1:00:00 AM​
0.671​
1/24/2020​
2:00:00 AM​
0.751​
1/24/2020​
3:00:00 AM​
0.668​
1/24/2020​
4:00:00 AM​
0.648​
1/24/2020​
5:00:00 AM​
0.615​
1/25/2020​
1:00:00 AM​
0.678​
1/25/2020​
2:00:00 AM​
0.657​
1/25/2020​
3:00:00 AM​
0.684​
1/25/2020​
4:00:00 AM​
0.615​
1/25/2020​
5:00:00 AM​
0.612​
1/26/2020​
1:00:00 AM​
0.536​
1/26/2020​
2:00:00 AM​
0.598​
1/26/2020​
3:00:00 AM​
0.551​
1/26/2020​
4:00:00 AM​
0.54​
1/26/2020​
5:00:00 AM​
0.5​
1/27/2020​
1:00:00 AM​
0.594​
1/27/2020​
2:00:00 AM​
0.64​
1/27/2020​
3:00:00 AM​
0.615​
1/27/2020​
4:00:00 AM​
0.584​
1/27/2020​
5:00:00 AM​
0.573​
1/28/2020​
1:00:00 AM​
0.564​
1/28/2020​
2:00:00 AM​
0.558​
1/28/2020​
3:00:00 AM​
0.625​
1/28/2020​
4:00:00 AM​
0.628​
1/28/2020​
5:00:00 AM​
0.558​
1/30/2020​
1:00:00 AM​
0.833​
1/30/2020​
2:00:00 AM​
0.836​
1/30/2020​
3:00:00 AM​
0.718​
1/30/2020​
4:00:00 AM​
0.658​
1/30/2020​
5:00:00 AM​
0.633​
1/31/2020​
1:00:00 AM​
0.671​
1/31/2020​
2:00:00 AM​
0.626​
1/31/2020​
3:00:00 AM​
0.618​
1/31/2020​
4:00:00 AM​
0.663​
1/31/2020​
5:00:00 AM​
0.6​
2/1/2020​
1:00:00 AM​
0.686​
2/1/2020​
2:00:00 AM​
0.689​
2/1/2020​
3:00:00 AM​
0.67​
2/1/2020​
4:00:00 AM​
0.678​
2/1/2020​
5:00:00 AM​
0.686​
2/2/2020​
1:00:00 AM​
0.682​
2/2/2020​
2:00:00 AM​
0.644​
2/2/2020​
3:00:00 AM​
0.697​
2/2/2020​
4:00:00 AM​
0.64​
2/2/2020​
5:00:00 AM​
0.625​
2/3/2020​
1:00:00 AM​
0.567​
2/3/2020​
2:00:00 AM​
0.563​
2/3/2020​
3:00:00 AM​
0.529​
2/3/2020​
4:00:00 AM​
0.55​
2/3/2020​
5:00:00 AM​
0.504​
2/4/2020​
1:00:00 AM​
0.633​
2/4/2020​
2:00:00 AM​
0.679​
2/4/2020​
3:00:00 AM​
0.539​
2/4/2020​
4:00:00 AM​
0.626​
2/4/2020​
5:00:00 AM​
0.549​
2/5/2020​
1:00:00 AM​
0.49​
2/5/2020​
2:00:00 AM​
0.461​
2/5/2020​
3:00:00 AM​
0.469​
2/5/2020​
4:00:00 AM​
0.484​
2/5/2020​
5:00:00 AM​
0.493​
2/6/2020​
1:00:00 AM​
0.843​
2/6/2020​
2:00:00 AM​
0.776​
2/6/2020​
3:00:00 AM​
0.824​
2/6/2020​
4:00:00 AM​
0.689​
2/6/2020​
5:00:00 AM​
0.617​
2/7/2020​
1:00:00 AM​
0.663​
2/7/2020​
2:00:00 AM​
0.701​
2/7/2020​
3:00:00 AM​
0.834​
2/7/2020​
4:00:00 AM​
0.658​
2/7/2020​
5:00:00 AM​
0.701​
2/8/2020​
1:00:00 AM​
0.736​
2/8/2020​
2:00:00 AM​
0.731​
2/8/2020​
3:00:00 AM​
0.627​
2/8/2020​
4:00:00 AM​
0.615​
2/8/2020​
5:00:00 AM​
0.656​
2/9/2020​
1:00:00 AM​
0.47​
2/9/2020​
2:00:00 AM​
0.521​
2/9/2020​
3:00:00 AM​
0.508​
2/9/2020​
4:00:00 AM​
0.522​
2/9/2020​
5:00:00 AM​
0.468​
2/10/2020​
1:00:00 AM​
0.702​
2/10/2020​
2:00:00 AM​
0.61​
2/10/2020​
3:00:00 AM​
0.561​
2/10/2020​
4:00:00 AM​
0.584​
2/10/2020​
5:00:00 AM​
0.556​
2/11/2020​
1:00:00 AM​
0.617​
2/11/2020​
2:00:00 AM​
0.641​
2/11/2020​
3:00:00 AM​
0.602​
2/11/2020​
4:00:00 AM​
0.579​
2/11/2020​
5:00:00 AM​
0.55​
2/12/2020​
1:00:00 AM​
0.566​
2/12/2020​
2:00:00 AM​
0.59​
2/12/2020​
3:00:00 AM​
0.575​
2/12/2020​
4:00:00 AM​
0.561​
2/12/2020​
5:00:00 AM​
0.6​
2/13/2020​
1:00:00 AM​
0.63​
2/13/2020​
2:00:00 AM​
0.602​
2/13/2020​
3:00:00 AM​
0.616​
2/13/2020​
4:00:00 AM​
0.549​
2/13/2020​
5:00:00 AM​
0.546​
2/14/2020​
1:00:00 AM​
0.916​
2/14/2020​
2:00:00 AM​
0.982​
2/14/2020​
3:00:00 AM​
0.932​
2/14/2020​
4:00:00 AM​
0.829​
2/14/2020​
5:00:00 AM​
0.831​
2/15/2020​
1:00:00 AM​
0.743​
2/15/2020​
2:00:00 AM​
0.787​
2/15/2020​
3:00:00 AM​
0.742​
2/15/2020​
4:00:00 AM​
0.753​
2/15/2020​
5:00:00 AM​
0.719​
2/16/2020​
1:00:00 AM​
0.554​
2/16/2020​
2:00:00 AM​
0.632​
2/16/2020​
3:00:00 AM​
0.702​
2/16/2020​
4:00:00 AM​
0.686​
2/16/2020​
5:00:00 AM​
0.686​
2/17/2020​
1:00:00 AM​
0.464​
2/17/2020​
2:00:00 AM​
0.5​
2/17/2020​
3:00:00 AM​
0.388​
2/17/2020​
4:00:00 AM​
0.458​
2/17/2020​
5:00:00 AM​
0.525​
2/18/2020​
1:00:00 AM​
0.631​
2/18/2020​
2:00:00 AM​
0.615​
2/18/2020​
3:00:00 AM​
0.562​
2/18/2020​
4:00:00 AM​
0.533​
2/18/2020​
5:00:00 AM​
0.476​
2/19/2020​
1:00:00 AM​
0.58​
2/19/2020​
2:00:00 AM​
0.65​
2/19/2020​
3:00:00 AM​
0.632​
2/19/2020​
4:00:00 AM​
0.686​
2/19/2020​
5:00:00 AM​
0.668​
2/20/2020​
1:00:00 AM​
0.695​
2/20/2020​
2:00:00 AM​
0.807​
2/20/2020​
3:00:00 AM​
0.768​
2/20/2020​
4:00:00 AM​
0.69​
2/20/2020​
5:00:00 AM​
0.734​
2/21/2020​
1:00:00 AM​
0.874​
2/21/2020​
2:00:00 AM​
0.763​
2/21/2020​
3:00:00 AM​
0.8​
2/21/2020​
4:00:00 AM​
0.743​
2/21/2020​
5:00:00 AM​
0.716​
2/22/2020​
1:00:00 AM​
0.761​
2/22/2020​
2:00:00 AM​
0.71​
2/22/2020​
3:00:00 AM​
0.679​
2/22/2020​
4:00:00 AM​
0.673​
2/22/2020​
5:00:00 AM​
0.607​
2/23/2020​
1:00:00 AM​
0.501​
2/23/2020​
2:00:00 AM​
0.578​
2/23/2020​
3:00:00 AM​
0.448​
2/23/2020​
4:00:00 AM​
0.525​
2/23/2020​
5:00:00 AM​
0.546​
2/24/2020​
1:00:00 AM​
0.604​
2/24/2020​
2:00:00 AM​
0.693​
2/24/2020​
3:00:00 AM​
0.546​
2/24/2020​
4:00:00 AM​
0.619​
2/24/2020​
5:00:00 AM​
0.429​
2/25/2020​
1:00:00 AM​
0.565​
2/25/2020​
2:00:00 AM​
0.52​
2/25/2020​
3:00:00 AM​
0.578​
2/25/2020​
4:00:00 AM​
0.444​
2/25/2020​
5:00:00 AM​
0.515​
2/27/2020​
1:00:00 AM​
0.529​
2/27/2020​
2:00:00 AM​
0.575​
2/27/2020​
3:00:00 AM​
0.585​
2/27/2020​
4:00:00 AM​
0.736​
2/27/2020​
5:00:00 AM​
0.598​
2/28/2020​
1:00:00 AM​
0.605​
2/28/2020​
2:00:00 AM​
0.619​
2/28/2020​
3:00:00 AM​
0.679​
2/28/2020​
4:00:00 AM​
0.623​
2/28/2020​
5:00:00 AM​
0.593​
2/29/2020​
1:00:00 AM​
0.769​
2/29/2020​
2:00:00 AM​
0.826​
2/29/2020​
3:00:00 AM​
0.76​
2/29/2020​
4:00:00 AM​
0.843​
2/29/2020​
5:00:00 AM​
0.762​
1/8/2020​
1:00:00 AM​
0.831​
1/8/2020​
2:00:00 AM​
0.742​
1/8/2020​
3:00:00 AM​
0.734​
1/8/2020​
4:00:00 AM​
0.669​
1/8/2020​
5:00:00 AM​
0.633​
1/9/2020​
1:00:00 AM​
1.317​
1/9/2020​
2:00:00 AM​
1.301​
1/9/2020​
3:00:00 AM​
1.28​
1/9/2020​
4:00:00 AM​
1.269​
1/9/2020​
5:00:00 AM​
1.222​
1/10/2020​
1:00:00 AM​
0.272​
1/10/2020​
2:00:00 AM​
0.277​
1/10/2020​
3:00:00 AM​
0.378​
1/10/2020​
4:00:00 AM​
0.255​
1/10/2020​
5:00:00 AM​
0.264​
1/11/2020​
1:00:00 AM​
0.164​
1/11/2020​
2:00:00 AM​
0.261​
1/11/2020​
3:00:00 AM​
0.27​
1/11/2020​
4:00:00 AM​
0.274​
1/11/2020​
5:00:00 AM​
0.237​
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,680
Office Version
  1. 365
Platform
  1. MacOS
=SUMIFS(C:C,A:A,">="&DATEVALUE("1-jan-2021"),A:A,"<="&DATEVALUE("31-dec-2021"))

there are different ways to get the date
But see if that gives you the result you want

Dates need to be real dates and not text - check that by format cells general - should display a number

Book2
ABCD
1DayHour of DayHourlyTotal
2Friday, 1 January 20211:00:00 AM0.7173.081
3Friday, 1 January 20212:00:00 AM0.798
4Monday, 20 December 20213:00:00 AM0.866
5441974:00:00 AM0.7
Sheet1
Cell Formulas
RangeFormula
D2D2=SUMIFS(C:C,A:A,">="&DATEVALUE("1-jan-2021"),A:A,"<="&DATEVALUE("31-dec-2021"))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,122
Messages
5,640,242
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top