Microsoft Excel Formula for seperating time into different colums.

Question

Please help, i need to seperate time differences between to date and time values, eg..

20/05/2008 14:00 20/05/2008 20:00

21/05/2008 06:00 21/05/2008 19:00

The two time differences above need to be sepearted so that if the time is before 7am or after 7pm the difference between the left and right cell value will be in one cell and if the statement is false the difference will go into another cell.

Does anyone know what formula to use in Excel?

Answer 1

Given the following date/time cells:

A1: 01/01/2001 06:00

B1: 01/01/2001 22:00

To extract time from A1:

=MOD(A1, 1)

result: 06:00:00 AM

To turn a time value into hours:

=("7:00 PM")*24

result: 19

In C1, to determine how many hours are between 7am and 7pm:

=(MIN("19:00", MOD(B1, 1))- MAX("7:00", MOD(A1, 1))) *24

result: 12.00

In D1, to determine how many hours are NOT between 7am and 7pm:

=(B1- A1- C1/24) *24

result: 4.00

Not sure how you came up with a result of 2 hours for D1, as 6 am is 1 hour extra and 10 pm would be 3 hours extra.

The formulas assume that the times are on the same day.  If you need the formula to work for times that may span two different dates, message me and I can solve that for you.

Answer 2

Im not sure how your data is set up.

Assuming that the time is in different cells, you want the difference of the two cells in one column/cell, but if the "time test" is false, then the difference will go to a different column/cell.

7 AM=700.  7 PM=1900.

You'll need two formulas in two different column/cells.

Use the , IF(AND( statement.

=IF(AND(Time A<=700,Time B=>1900, Time B - Time A, ""))

This formula will place the time difference in one cell and if the tests are false it places a void in that cell, ("").

The second formula would look like this, in a different cell.

=IF(AND(Time A=>700, Time B<=1900, Time B - Time A, ""))

These test that the time is equal and more than or less than.

If you want the test to only be true when the time is less or more, then delete the equal sign.

I'm not certain that this is what you want, but the IF AND statement is needed.

Answer 3

date and time are stored as a number in excel,  the first date/time is actually stored as 39588.5833333333 . days are worth 1 and an hour is 1/24th. You can use this to just do simple maths on the cells.

if(a1<(b1-0.5),do something,do something else) would determine if the first time is more than 12 hours before the second time.