Convert time to duty day in Excel

Manufacturing data is recorded in time (in seconds), but it is often necessary to display the shift date for graphing or analysis. So what I want to share with you today is how to replace the actual date and time with the duty date.

Friends who have not worked in the factory may not know. For a factory that operates 24 hours a day, 7 days a week, operators need to work 12 hours in shifts. People on night shift work from 7:30pm to 7:30am the next morning. Before 7:30am, the duty day is the day before. For example, it is now 11:17 pm on April 29th, and after 12:00 pm, it will be April 30th, but the duty date is still April 29th until 7:30 am.

At this point, you may already know the difficulty of this trick, which is to make the date between 12 midnight and 7:30 am displayed as the previous day. Usually there are two types of data:

🔷The first type: date and time in different columns (column)

Date of duty: Enter "=IF(B2<0.3125,A2-1,A2)" in cell C2 to get the date of duty

❇️ Instructions for using functions

• IF is used to judge whether the time is less than 7:30, if so, take the date of the previous day, otherwise use it directly
• 0.3125 represents how many days are 7.5 hours, calculated by dividing 7.5 hours by 24 hours (7.5/24)

🔷The second: date and time in the same column

Semi-finished product: input =(IF(HOUR(A2)*100+MINUTE(A2)<730,A2-1,A2)) in B2
Date of duty: enter =TEXT(B2,"yyyy-mm-dd")-0 in cell C2

❇️ Instructions for the use of semi-finished products

• This is more complicated. In order to simplify the process, we use one more column to make semi-finished products. In addition to using the IF function, this semi-finished product also uses the HOUR and MINUTE functions.
• HOUR and MINUTE can help us get a few points respectively. The example is 1:41 in the morning. We use HOUR to get 1 and MINUTE to get 41, so we multiply 1 by 100 and add minutes, which is equal to 141, which is less than 730 ( shift time), so the date needs to be decremented by one, and vice versa.

❇️ Function description for duty date

• Although the date of the semi-finished product is correct, it has time attached, so we need to use the TEXT function to remove the time. The yyyy-mm-dd in the example indicates that the format to be displayed is year, month, day, and the final subtraction of zero is to convert the text format back to the number format. If you don't need the number format you can skip the subtraction of zeros.

In order to facilitate understanding, the Excel file is attached for reference, click me to download . This is the end of today's content. If there is anything unclear, please leave a message and I will reply as soon as possible. Thank you for reading this.