Microsoft Excel 2003 (full)

I need to find the time between times! Basicly we get a referal then go see the patient... I need to be able to report on the time scale it takes us to get to the patient. I have recorded the times in 1 cell as follows:

Referal recieved

B1 04/09/2009 01:30:00 PM

Patient visited

D1 04/09/2009 02:10:00 PM.

Some of the times the patient can be visited on the same day someitmes it can be a couple of weeks/months. I have made a couple of formulas but they arnt showing what I need! also I need to pull a report on the times and need some help doing that too!Any help is greatly apprechiated!

Try this in Cell E1: =DATEDIF(B1,D1 -(MOD(B1,1)>MOD(D1,1)),"y")&" years, "&DATEDIF(B1,D1 -(MOD(B1,1)>MOD(D1,1)),"ym")&" months, "&DATEDIF(B1,D1 -(MOD(B1,1)>MOD(D1,1)),"md")&" days, "&TEXT(MOD(D1-B1,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""")

It is a very long formula, make sure when you copy it, everything is on one line.

MIKE

Hi, First some background.

Excel stores dates and times in one number with the date as the integer or whole number part and the data as the decimal part.

In this system 1 = 1 day.

Excel's default data system has 01 January 1900 as 1.

40260 is 23 March 2010, that is 40259 days after 01 January 1900.40260.5 is 12 noon on 23 March, i.e., half way through the day.

The next important thing to understand is that what you see in a cell containing a date / time or date+time depends on the cell format.

If you have a cell containing 40260.5 and format it in different ways it will display very different values

Format "dd/mmm/yy" and it displays 23/Mar/10

Format "hh:mm" and it displays 12:00Time and date differences can be obtained by simple subtraction.

The results are numbers which have integer and decimal parts.

The integer part this time is the number of completed days difference and the decimal part is the remaining hours and minutes difference.

Noon on 23 March minus noon on 22 March is 1 day difference and the result is 1.00

Format it as "hh:mm" and it shows 0 ! This is because there is one day difference and no remaining hours or minutes.

But Excel has a format for this ! Use [h]:mm as the format and it shows 24:00Of course because the difference is one day and the cell contains 1, if you format it as dd/mmm/yyyy you will get the date 01/Jan/1900

In summary simple addition and subtraction works just fine, but what you see depends on the format applied to the cell.

Now for some practical applications

In two columns are dates and times A & B

A is the registered or start date & time and B is the date & time visited.

Taking the first pair on row 2:

In cell C2 is the formula =B2-A2, and the format is d

In cell D2 is the same formula =B2-A2 and the format is [h]:mm

If A2 contains 04/Sep/09 13:30 and

B2 contains 04/Sep/09 14:10

C2 will display 0 as there is less than 1 day (24 hours between them - even if we crossed midnight into the next day it would still be 0)

D2 shows 0:40 as the times are just 42 minutes apart.The following is a table of dates and times using the B2-A2 formula and the two formats described d and [h]:mm

A B C D 1 Registered Visited Days Hours/minutes 2 04/Sep/09 13:30 04/Sep/09 14:10 0 0:40 3 05/Sep/09 01:30 05/Sep/09 11:59 0 10:29 4 05/Sep/09 08:42 05/Sep/09 16:20 0 7:38 5 05/Sep/09 11:06 06/Sep/09 05:26 0 18:20 6 05/Sep/09 23:06 06/Sep/09 14:10 0 15:04 7 06/Sep/09 08:42 06/Sep/09 22:53 0 14:11 8 04/Sep/09 06:18 05/Sep/09 23:46 1 41:28 9 02/Sep/09 13:30 06/Sep/09 04:34 3 87:04 10 02/Sep/09 15:15 07/Sep/09 06:04 4 110:49 11 31/Aug/09 13:30 07/Sep/09 14:10 7 168:40 12 29/Aug/09 11:06 08/Sep/09 23:46 10 252:40 13 28/Aug/09 06:18 10/Sep/09 09:22 13 315:04 14 26/Aug/09 01:30 10/Sep/09 23:46 15 382:16 15 24/Aug/09 23:06 12/Sep/09 11:46 18 444:40 16 24/Aug/09 11:06 12/Sep/09 14:10 19 459:04From this you can create some statistics. The following are statistics from the above results, after the table are some sample formulas.

17 A B C D 18 Total (N) 15 19 20 Stats n % 21 1 to <2 days 1 6.67 22 2 to <5days 2 13.33 23 5 to <10 days 1 6.67 24 10 days or more 5 33.33 25 26 Less than 1 day 27 <1 hour 1 6.67 28 1 to <4 hours 0 0.00 29 4 to <8 hours 1 6.67 30 8 to <12 hours 1 6.67 31 12 to <18 hours 2 13.33 32 18 to 24 hours 1 6.67 33 34 Average (hours:mins) 155:12 35 Max (hours:mins) 459:04

The total in cell C18 is:=COUNTA(C2:C16)

The number of cases that had a duration of at least 1 day (24 hours) but less than 2 days is shown in cell C21 which contains =SUMPRODUCT(($C$2:$C$16>=1)*($C$2:$C$16<2))

C22 contains =SUMPRODUCT(($C$2:$C$16>=2)*($C$2:$C$16<5))

so you can see how the formula can be used for any period of days

C24 is for 10 or more days and is slightly different =SUMPRODUCT(($C$2:$C$16>=10)*(1))

The numbers in each duration of less than 1 day used the hours value in column D

<1 hour in cell C27 contains =SUMPRODUCT(($D$2:$D$16>0)*($D$2:$D$16<(1/24)))

Note that as times less than 24 hours or 1 day are decimal values. 1 hour is 1 day / 24 hence the 1/24

C28 is =SUMPRODUCT(($D$2:$D$16>(1/24))*($D$2:$D$16<(4/24)))The percentages are produced by dividing the number in a category by the total count (in cell C18)

For example cell D21 contains =C27/$C$18*100

This formula can just be dragged down alongside the values in column C to give all the percentages. The $ signs in the $C$18 part of the formula ensure that all the formulas refer to the Count value - $C$18 will not change when the cell/formula is dragged down.The Average in cell C34 is =AVERAGE(D2:D16)

and the Maximum interval in C35 is =MAX(D2:D16)I hope that this gives you a starting point for creating a recording and reporting system.

If you would like this spreadsheet please send me a Private message with your e-mail and I will send it to you.

(please do not post your e-mail address in a public message).Regards

Firstly thank you for the replys and help. Humar

I have sent you a message with my email on. Thank you for the explanation thats helped explain how it works and I have a better and understanding the code/formula. I have tried and completed my spreadsheet and been able to get the report that I needed! Thank you!Mike

I give it a try and the formula from Humar was little a cleaner and easier to understand! So I have used his coding! Thank you for your help though it is much appreciated!Once again thank you for the replay/help.

Adam

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History