Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
I've serched and found some answers, but I'm just not sure If I doning
correctly. I'm trying to have the .xls calculate the amount of time the user was connected. I currently have the following: D22 D23 16-Aug-06 7:30:00 PM (This is when the User Connected) 17-Aug-06 5:30:00 AM (This is when the user Disconnected) My current formula is: =(E23-E22+(E23<E22))*24 Which gives me 240:00:00 But I want to see that that user was connected for 10:00:00 What am I doing wrong? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
You do not need to multiply by 24 in your formula.
Hope this helps. Pete Dtown Dawg wrote: I've serched and found some answers, but I'm just not sure If I doning correctly. I'm trying to have the .xls calculate the amount of time the user was connected. I currently have the following: D22 D23 16-Aug-06 7:30:00 PM (This is when the User Connected) 17-Aug-06 5:30:00 AM (This is when the user Disconnected) My current formula is: =(E23-E22+(E23<E22))*24 Which gives me 240:00:00 But I want to see that that user was connected for 10:00:00 What am I doing wrong? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
With your sample data in A1:B2, try this formula in C2:
=((A2+B2)-(A1+B1))*24 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
If E22 contains both the date and time 8/16/2006 7:30 PM, and E23 contain
8/17/2006 5:30 AM, then all you need is this formula =E23-E22 The formula you are using is for timesheets that allows for data to be entered as time only (without the date). It multiplies by 24 to get a decimal answer (10.25 instead of 10:15). "Dtown Dawg" wrote: I've serched and found some answers, but I'm just not sure If I doning correctly. I'm trying to have the .xls calculate the amount of time the user was connected. I currently have the following: D22 D23 16-Aug-06 7:30:00 PM (This is when the User Connected) 17-Aug-06 5:30:00 AM (This is when the user Disconnected) My current formula is: =(E23-E22+(E23<E22))*24 Which gives me 240:00:00 But I want to see that that user was connected for 10:00:00 What am I doing wrong? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
If I don't multiply by 24 and only use (E23-E22) then the answer I get is:
################ So that didn't work. "Pete_UK" wrote: You do not need to multiply by 24 in your formula. Hope this helps. Pete Dtown Dawg wrote: I've serched and found some answers, but I'm just not sure If I doning correctly. I'm trying to have the .xls calculate the amount of time the user was connected. I currently have the following: D22 D23 16-Aug-06 7:30:00 PM (This is when the User Connected) 17-Aug-06 5:30:00 AM (This is when the user Disconnected) My current formula is: =(E23-E22+(E23<E22))*24 Which gives me 240:00:00 But I want to see that that user was connected for 10:00:00 What am I doing wrong? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
Actually I just went in and formated the cell as a number instead of time and
it works just fine. "Dave O" wrote: With your sample data in A1:B2, try this formula in C2: =((A2+B2)-(A1+B1))*24 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help Calculate Time Between 2 different Dates
E22 and E23 probably only contain time without the dates. You are getting a
negative value when you subtract 5:30 AM from 7:30 PM. In your example you listed the dates with the times so I assumed they were together. I geuss your table might look like this D E 22 8/16/06 7:30 PM 23 8/17/06 5:30 AM In which case you can use =D23+E23-D22-E22 If the user won't be connected for more than 24 hours then you can use the original formula (without the *24), otherwise you will need this one. "Dtown Dawg" wrote: If I don't multiply by 24 and only use (E23-E22) then the answer I get is: ################ So that didn't work. "Pete_UK" wrote: You do not need to multiply by 24 in your formula. Hope this helps. Pete Dtown Dawg wrote: I've serched and found some answers, but I'm just not sure If I doning correctly. I'm trying to have the .xls calculate the amount of time the user was connected. I currently have the following: D22 D23 16-Aug-06 7:30:00 PM (This is when the User Connected) 17-Aug-06 5:30:00 AM (This is when the user Disconnected) My current formula is: =(E23-E22+(E23<E22))*24 Which gives me 240:00:00 But I want to see that that user was connected for 10:00:00 What am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can excel calculate time increments in a formula? | Excel Worksheet Functions | |||
formula to calculate time difference crossing midnight | Excel Worksheet Functions | |||
Importing dates and military time from Excel into word doc mail me | Excel Discussion (Misc queries) | |||
calculate total service time in excel | Excel Discussion (Misc queries) | |||
calculate elapsed time between dates and times | Excel Worksheet Functions |