Shulou(Shulou.com)11/24 Report--

In daily work, the calculation of call time, or the calculation of parking hours in the business district, etc., are very common.

Some of them charge by the minute.

For example: telecom call time, less than 1 minute is calculated as 1 minute.

Some are calculated according to half an hour or an hour.

For example: business district parking charge, less than half an hour or less than an hour will be calculated as half an hour or one hour and so on.

Today, we will share the problems and solutions calculated by the length of time.

1. The requirements of the question are shown in the following figure, which is a table for calculating the length of time:

The requirement is to calculate the number of minutes between the start time and the end time. Less than one minute is counted as one minute.

For example:

The starting time is 10:30:35

The end time is 10:38:59

The time difference between them is: 8 minutes 24 seconds

Less than one minute is counted as one minute, and the final result is: 9 minutes.

2. Problem-solving methods from the above requirements, problem-solving ideas can be divided into the following two types:

In general, the simplest and best understood thing about ▋ method 1 is to use the time function to handle it.

For example, Hour takes hours, Minute takes minutes, and Second takes seconds.

As shown in the following figure: we can enter the following formula in [D3:D6] cell:

= HOUR (C3-B3) * 60+MINUTE (C3-B3) + (SECOND (C3-B3 > 0) formula resolution:

❶ subtracts the start time of [B3] from the end time of [C3], and then uses the Hour function to take the number of hours and multiply it by 60 to get the number of minutes.

❷ subtracts the start time of [B3] from the end time of [C3], and then uses the Minute function to take out the number of minutes and directly get the number of minutes.

❸ subtracts the start time of [B3] from the end time of [C3], and then uses the Second function to take the number of seconds and determine whether it is greater than 0. If it is greater than 0, it returns TRUE and uses addition to make it 1 minute. If it is less than or equal to 0, return False and add it to 0 minutes.

As a result, the purpose of less than 1 minute, calculated according to 1 minute is achieved.

▋ method 2 actually, method 1 only calculates the length of time in principle.

We can also easily solve this kind of problem by replacing it with a function (Ceiling function) that is specifically used to calculate carry.

Its syntax is as follows:

= CEILING (number, significance)

= CEILING (value to be rounded, multiple)

Translated into Chinese is:

The value to be rounded up to the specified multiple.

Then, the above calculation can be changed to:

[D3] the formula is as follows:

= CEILING (C3murB3TIME (0mem1mem0)) * 1440 Formula Analysis:

Subtract the start time from the end time, then round it up in multiples of one minute, and then multiply it by 1440 minutes.

Where: the Time function is a function that represents time.

Its syntax is as follows:

= Time (hours, minutes, seconds)

Here is to press 1 minute as a multiple, so both hours and seconds are 0.

Here 1440 means: 24 hours in a day and 60 minutes in an hour, that is, 24 hours, 60 minutes, 1440 hours.

The formula here can be further simplified and directly rewritten as shown below:

= CEILING ((C3-B3) * 1440 ~ (1)) Formula Analysis:

Subtract the start time from the end time, then multiply it by 1440 minutes, and then round up to the whole number by a multiple of 1.

Two solutions are for your reference.

3. Knowledge expansion ❶ in our daily work, we may also be exposed to using the Text function to obtain and process time.

As shown below:

Formula:

= TEXT (C3-B3, "[m]") first subtracts the start time from the end time, and then uses the Text function to fetch the number of minutes between.

However, friends should pay special attention to this:

Here, use the Text function to take out the number of minutes, which is directly truncated to take the integer.

That is, it only cares about the number of hours and minutes, regardless of the number of seconds or not.

In addition: when you take the number of minutes, do not just write an M.

As shown below:

The formula is as follows:

= TEXT (C3-B3, "m") if you write only one letter M at this time, it will only take out the number of months in which two times are subtracted, not minutes.

The date corresponding to the time here defaults to January 0, 1900.

So take the number of months, and the result returns 1.

When ❷ uses the Ceiling function, the following error result may occur.

As shown below:

The formula is exactly the same as above:

= CEILING ((C3-B3) * 1440 minute 1) the end time minus the start time should be 10 minutes, but the result is the wrong 11 minutes.

We can take a look at the operation result of the first parameter in the Ceiling function, as shown in the following figure:

The result of the operation produces a very small tail error, so it is rounded up to 11 minutes.

The solution can be rounded using the Round function.

The revised formula is as follows:

= CEILING (ROUND ((C3-B3) * 1440 C3-B3 6), 1) here we use the Round function to retain 6 decimal places, and those with more than 6 bits are rounded directly.

The final result shows that it is correct!

4. at the end of today, we share the common methods and ideas of calculating the length of time in our daily work.

❶ can use the most basic time function to deal with the length of time. For example: Hour\ Minute\ Second and so on.

❷ can also be handled by Microsoft's special function Ceiling designed specifically for this problem.

It's just that when dealing with it, we should pay special attention to the tail error mentioned above, otherwise it will easily lead to mistakes.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.