Forum Discussion

JakeOliver's avatar
JakeOliver
Copper Contributor
Oct 24, 2024
Solved

COUNTIFS Automatically pick up cell

I have built a worksheet using countifs to counts the number of transits ships make in and out of the port with 3 criteria. As we are monitoring across a 12 month period for compliance the workbook i...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 12, 2024

    As variant it could be

    =IF( [@[Date & Time]] = "", "-",
 IF( ( MOD([@[Date & Time]],1) >= 8/24 ) *
     ( MOD([@[Date & Time]],1) < 20/24 ),
     "Day", "Night")
 )

    (see in December tab), but it depends what do you consider as Day and what as Night.

    Here MOD([@[Date & Time]],1) extracts time part of datetime, 8/24 means 8am, 20/24 means 8pm. Better not to hardcode above but move into parameters (named cells).

    Before inserting the formula delete values in all cells of the column B within the table, with that formula will be applied automatically to all cells here. Includes the situation if you resize the table.

JakeOliver's avatar
JakeOliver
Copper Contributor
Nov 12, 2024

SergeiBaklan 

 

I have been working on the work book further with our radio operators. 

We have created the date and time drop down they asked for. 

Column B has an option to select Day/Night. Is it possible to automate this based on a pre-defined time range, so that the cell fills with either day or night? 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 12, 2024

As variant it could be

=IF( [@[Date & Time]] = "", "-",
 IF( ( MOD([@[Date & Time]],1) >= 8/24 ) *
     ( MOD([@[Date & Time]],1) < 20/24 ),
     "Day", "Night")
 )

(see in December tab), but it depends what do you consider as Day and what as Night.

Here MOD([@[Date & Time]],1) extracts time part of datetime, 8/24 means 8am, 20/24 means 8pm. Better not to hardcode above but move into parameters (named cells).

Before inserting the formula delete values in all cells of the column B within the table, with that formula will be applied automatically to all cells here. Includes the situation if you resize the table.

Resources