SOLVED

I need a Custom Date And Time Calculating formula

Copper Contributor

I need a Custom Date And Time Calculating formula

But i need here 18.50 hoursI want to calculate the total working hours of an employee who will start working on 23/12/2023 at 08:00 AM and end on 24/12/2023 at 05:30 PM. However, Our working hours are split into two shifts - from 08:00 AM to 01:00 PM and from 02:00 PM to 07:00 PM.

You have noticed that the formula we used to calculate the total hours (End Time minus Starting Work) gives us an incorrect value of 33:30. Instead, we need to consider the breaks in between.

To calculate the actual working hours based on our schedule, we need to break down the time worked into separate shifts and subtract the break times. Here is how we can calculate the total working hours for this employee:

- On 23/12/2023:

- The employee starts working at 08:00 AM and stops at 01:00 PM for a launch break. This means a total of 5 hours worked.

- The employee starts working again at 02:00 PM and stops at 07:00 PM when the working hours end. This means another 5 hours worked.

- On 24/12/2023:

- The employee starts working at 08:00 AM and stops at 01:00 PM for a launch break. This means another 5 hours worked.

- The employee starts working again at 02:00 PM and stops at 05:30 PM when the working hours end. This means 3.5 hours worked.

The total working hours for this employee over the two days is 18.5 hours. I hope this helps! and waiting for the best formula thank you

5 Replies

Re: I need a Custom Date And Time Calculating formula

To make this flexible to adding more shifts or changing the shift hours, create a table called "shifts" with three columns:

1. start

2. end

3. shift

Populate the table as shown in the image.

Then you can use this formula for your calculation:

``````=LET(
start,B4,
end,C4,
mins,SEQUENCE(((end-start)*1440),,start,1/(24*60)),
xl,XLOOKUP(mins-INT(mins),shifts[start],shifts[shift],,-1),
COUNT(xl)/60
)``````

table name: shifts

 start end shift 12:00 AM 8:00 AM #N/A 8:00 AM 1:00 PM 1 1:00 PM 2:00 PM #N/A 2:00 PM 7:00 PM 2 7:00 PM 12:00 AM #N/A

Re: I need a Custom Date And Time Calculating formula

any one can help me in this one
best response confirmed by Mahammad_Asim (Copper Contributor)
Solution

Re: I need a Custom Date And Time Calculating formula

What is the problem?  As you will see in the attached workbook, I re-created the range data that you showed, created the shifts table very similar to what @flexyourdata showed (though I put it on a different worksheet, and the #N/A values are not required), and entered their formula (adjusting it to reference the correct cells, plus some cleanup) into column H.  It produces the correct number of hours (but not an Excel time value) for Excel 365 for Windows, and I expect it to work for the web version, also.

The @flexyourdata formulas create an internal array (named mins) for each minute between the Start date-time and Stop date-time, and then use a derivative of that array (without dates) to do lookups into the shifts table to create another array (named xl) of the shift identifiers (some possibly blank) for an identical time-of-day or (more likely) the latest time-of-day entry that is earlier.  (The end times in the table are just documentation; they are not used.) Unstated assumptions:

• The work shift identifiers will be numbers.
• You don't need a time precision smaller than a minute.

That technique is flexible.  However, it is performing many lookups for each formula recalculation. That may cause opening the workbook (or otherwise causing these formulas to recalculate) to be slow as you get many rows of data.  Faster alternatives are possible.

Maintenance note: In the future, if the work shifts are changed, you should either replace Work Time formulas for already-used rows with their values (as calculated under the older shift times), or set up a separate Excel table for the new shift times, and use that new table in formulas going forward.

Re: I need a Custom Date And Time Calculating formula

"When I enter the formula, it works properly. However, after saving the Excel sheet, an error occurs. An _xlfn. prefix appears in front of a function in the formula. When the formula is calculated, it displays the #NAME? error value."

Re: I need a Custom Date And Time Calculating formula

_xlfn means the function is not available in your version of Excel. Function shared above is for Excel 365, on which one you are?

1 best response

Accepted Solutions
best response confirmed by Mahammad_Asim (Copper Contributor)
Solution

Re: I need a Custom Date And Time Calculating formula

What is the problem?  As you will see in the attached workbook, I re-created the range data that you showed, created the shifts table very similar to what @flexyourdata showed (though I put it on a different worksheet, and the #N/A values are not required), and entered their formula (adjusting it to reference the correct cells, plus some cleanup) into column H.  It produces the correct number of hours (but not an Excel time value) for Excel 365 for Windows, and I expect it to work for the web version, also.

The @flexyourdata formulas create an internal array (named mins) for each minute between the Start date-time and Stop date-time, and then use a derivative of that array (without dates) to do lookups into the shifts table to create another array (named xl) of the shift identifiers (some possibly blank) for an identical time-of-day or (more likely) the latest time-of-day entry that is earlier.  (The end times in the table are just documentation; they are not used.) Unstated assumptions:

• The work shift identifiers will be numbers.
• You don't need a time precision smaller than a minute.

That technique is flexible.  However, it is performing many lookups for each formula recalculation. That may cause opening the workbook (or otherwise causing these formulas to recalculate) to be slow as you get many rows of data.  Faster alternatives are possible.

Maintenance note: In the future, if the work shifts are changed, you should either replace Work Time formulas for already-used rows with their values (as calculated under the older shift times), or set up a separate Excel table for the new shift times, and use that new table in formulas going forward.