| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date AND Time Differences
 Within 4 columns, I have a series of: Start Dates / Start Times / End Dates / End Times I need to create a formula which will give me the sum total of the difference between the data sets. For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours. I need a formula which will calculate this for me. I have driven the Paper Slide help function crazy by asking in numerous ways how to do this and I've also searched the forums as well as asking my resident Excel guy at work, but I cannot locate a solution.... Can anyone help ?? Regards, Lea777 -- Lea777 ------------------------------------------------------------------------ Lea777's Profile: http://www.excelforum.com/member.php...o&userid=32692 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
Start date in A2, start time in B2, end date in C2 and end time D2
03/20/06 in A2, 08:00 AM in B2, 03/21/06 in C2 and 01:00 PM =(C2+D2)-(A2+B2) format custom as [hh]:mm will return 29:00, format as d hh:mm will return 1 05:00 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Lea777" wrote in message ... Within 4 columns, I have a series of: Start Dates / Start Times / End Dates / End Times I need to create a formula which will give me the sum total of the difference between the data sets. For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours. I need a formula which will calculate this for me. I have driven the Paper Slide help function crazy by asking in numerous ways how to do this and I've also searched the forums as well as asking my resident Excel guy at work, but I cannot locate a solution.... Can anyone help ?? Regards, Lea777 -- Lea777 ------------------------------------------------------------------------ Lea777's Profile: http://www.excelforum.com/member.php...o&userid=32692 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
I might add that if you need to calculate an hourly rate you need to
multiply the result with 24 then the rate assume the formula is in F2 =F2*24*rate format as currency -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Peo Sjoblom" wrote in message ... Start date in A2, start time in B2, end date in C2 and end time D2 03/20/06 in A2, 08:00 AM in B2, 03/21/06 in C2 and 01:00 PM =(C2+D2)-(A2+B2) format custom as [hh]:mm will return 29:00, format as d hh:mm will return 1 05:00 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Lea777" wrote in message ... Within 4 columns, I have a series of: Start Dates / Start Times / End Dates / End Times I need to create a formula which will give me the sum total of the difference between the data sets. For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours. I need a formula which will calculate this for me. I have driven the Paper Slide help function crazy by asking in numerous ways how to do this and I've also searched the forums as well as asking my resident Excel guy at work, but I cannot locate a solution.... Can anyone help ?? Regards, Lea777 -- Lea777 ------------------------------------------------------------------------ Lea777's Profile: http://www.excelforum.com/member.php...o&userid=32692 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
Hi!
(End Date + End Time) - (Start Date + Start Time) Format the cell as [h]:mm for a return of 29:00 ((End Date + End Time) - (Start Date + Start Time))*24 Format the cell as GENERAL for a return of 29. Biff "Lea777" wrote in message ... Within 4 columns, I have a series of: Start Dates / Start Times / End Dates / End Times I need to create a formula which will give me the sum total of the difference between the data sets. For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours. I need a formula which will calculate this for me. I have driven the Paper Slide help function crazy by asking in numerous ways how to do this and I've also searched the forums as well as asking my resident Excel guy at work, but I cannot locate a solution.... Can anyone help ?? Regards, Lea777 -- Lea777 ------------------------------------------------------------------------ Lea777's Profile: http://www.excelforum.com/member.php...o&userid=32692 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
It worked guys, thank you so much, Lea777 :) -- Lea777 ------------------------------------------------------------------------ Lea777's Profile: http://www.excelforum.com/member.php...o&userid=32692 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
I have a different problem.I work in the BPO industry and I have an excel spreasheet that keeps track of every agents time on call.I want a spreadsheet column which will automatically display the current time when the agent clicks on the cell.Then I need another column in which the agents will click after they finish the call.And this cell should automatically display the current time.I will then use the time difference functions to calculate the time on call.Can anybody help ? -- harishkumar09 ------------------------------------------------------------------------ harishkumar09's Profile: http://www.excelforum.com/member.php...o&userid=34826 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
The current time can be entered by pressing "<control+<shift+;". To do it
by clicking on a cell would involve programming an event driven macro. You could ask in the programming group. -- Carlos "harishkumar09" wrote in message news:harishkumar09.28esu1_1148637608.3084@excelfor um-nospam.com... I have a different problem.I work in the BPO industry and I have an excel spreasheet that keeps track of every agents time on call.I want a spreadsheet column which will automatically display the current time when the agent clicks on the cell.Then I need another column in which the agents will click after they finish the call.And this cell should automatically display the current time.I will then use the time difference functions to calculate the time on call.Can anybody help ? -- harishkumar09 ------------------------------------------------------------------------ harishkumar09's Profile: http://www.excelforum.com/member.php...o&userid=34826 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
Thanks Carlos.Let me also tell you we use Star Office in our workplace and do the macros suggested in the programming section work for StarOffice as well ? -- harishkumar09 ------------------------------------------------------------------------ harishkumar09's Profile: http://www.excelforum.com/member.php...o&userid=34826 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
Sorry, I don't know anything about star office.
-- Carlos "harishkumar09" wrote in message news:harishkumar09.28ge0b_1148711707.5054@excelfor um-nospam.com... Thanks Carlos.Let me also tell you we use Star Office in our workplace and do the macros suggested in the programming section work for StarOffice as well ? -- harishkumar09 ------------------------------------------------------------------------ harishkumar09's Profile: http://www.excelforum.com/member.php...o&userid=34826 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
do any of you know where to get help for star office ? -- harishkumar09 ------------------------------------------------------------------------ harishkumar09's Profile: http://www.excelforum.com/member.php...o&userid=34826 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
Do a Google search for Star Office.
Hope this helps. Pete |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Date AND Time Differences
Maybe
staroffice.com ??? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "harishkumar09" wrote in message news:harishkumar09.28kthy_1148918402.414@excelforu m-nospam.com... do any of you know where to get help for star office ? -- harishkumar09 ------------------------------------------------------------------------ harishkumar09's Profile: http://www.excelforum.com/member.php...o&userid=34826 View this thread: http://www.excelforum.com/showthread...hreadid=525103 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
| calculating the time between one date and another | Excel Discussion (Misc queries) | |||
| How to calculate Date & Time differences | Excel Worksheet Functions | |||
| Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
| calculating date time ranges | Excel Worksheet Functions | |||
Linear Mode
