Formula to calculate on time percentage based on custom criteria [HELP]

99Mozart17

New Member
Joined
Jun 3, 2019
Messages
1
Hello,

I am brand new to excel and need some assistance on how to enter a formula that will calculate the percentage of how many days a week an employee clocked in at 10:05am and anytime before that.

May
05/01/19
10:06
05/02/1910:05
05/03/1910:11

<tbody>
</tbody>

<tbody>
</tbody>

This is what i have so far

COUNTIF(B3:B5)," <10:06 "

Red indicates that i am unsure if that is right.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,125
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try this:
Code:
=COUNTIF(B3:B5,"<"&(606/1440))

Here is the logic.
Date/Time is stored as a number in Excel. Date is the number of days since 1/0/1900 (whole numbers). Time is the fraction of one day.
There are 1440 minutes in a day (24*60). 10:06 AM is equal to 606 minutes (60 * 10 + 6) divided by 1440.
 
Last edited:

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

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