Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
I can't seem to find it in the help. How do I calculate time difference? We
have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Use the calculations that you indicated (A2-A1) but format the cell using the
following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
I'm still not getting it. It still has the same value and is giving #VALUE!
in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a
pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Try
=IF(A2A1,(A2-A1),(A2+24)-A1) -- Regards Warren "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Is there a way to calculate without having to go through and take out the AM
and PM? It's no difference that what I'm doing now. I enter the data in as 24 hour format manually. If I delete the PM, then it doesn't know to add 12 hours to the data. I want to have it convert that 4:43 PM to a 24 hour format automatically. Jason "Kevin B" wrote: Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Format the cell you enter the time into as a 24 hour clock format. You can
enter 4:33 pm and the format will modify the display to 16:33:00, but you cannot enter 16:33 pm as it's not a valid time entry. -- Kevin Backmann "jbly" wrote: Is there a way to calculate without having to go through and take out the AM and PM? It's no difference that what I'm doing now. I enter the data in as 24 hour format manually. If I delete the PM, then it doesn't know to add 12 hours to the data. I want to have it convert that 4:43 PM to a 24 hour format automatically. Jason "Kevin B" wrote: Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Why is that when I cut and paste the times from the report, it doesn't seem
to want to take the cell format? I did format the cells. I tried to cut/paste into the cell and it didn't work, but if I typed it in, it converted it. This is very frustrating. I've also been looking to see if maybe there is a function that will take the first five characters on the left of the cell or possilby even deleting the right three character in the cell to isolate the time without the AM/PM suffix. Jason "Kevin B" wrote: Format the cell you enter the time into as a 24 hour clock format. You can enter 4:33 pm and the format will modify the display to 16:33:00, but you cannot enter 16:33 pm as it's not a valid time entry. -- Kevin Backmann "jbly" wrote: Is there a way to calculate without having to go through and take out the AM and PM? It's no difference that what I'm doing now. I enter the data in as 24 hour format manually. If I delete the PM, then it doesn't know to add 12 hours to the data. I want to have it convert that 4:43 PM to a 24 hour format automatically. Jason "Kevin B" wrote: Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
It's the report that has the faulty format.
-- Regards, Peo Sjoblom "jbly" wrote in message ... Why is that when I cut and paste the times from the report, it doesn't seem to want to take the cell format? I did format the cells. I tried to cut/paste into the cell and it didn't work, but if I typed it in, it converted it. This is very frustrating. I've also been looking to see if maybe there is a function that will take the first five characters on the left of the cell or possilby even deleting the right three character in the cell to isolate the time without the AM/PM suffix. Jason "Kevin B" wrote: Format the cell you enter the time into as a 24 hour clock format. You can enter 4:33 pm and the format will modify the display to 16:33:00, but you cannot enter 16:33 pm as it's not a valid time entry. -- Kevin Backmann "jbly" wrote: Is there a way to calculate without having to go through and take out the AM and PM? It's no difference that what I'm doing now. I enter the data in as 24 hour format manually. If I delete the PM, then it doesn't know to add 12 hours to the data. I want to have it convert that 4:43 PM to a 24 hour format automatically. Jason "Kevin B" wrote: Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Is your report a text file? If so you can use the import wizard in Excel to
import the data into a workbook. Click FILE in the menu and select OPEN. In the OPEN FILE dialog box change the FILES OF TYPE to text files, locate the report file and click OPEN. This automatically activates the import wizard where you can tell Excel how to parse the data in the report. as for extracting text you can reference the following functions in HELP: LEFT RIGHT MID -- Kevin Backmann "jbly" wrote: Why is that when I cut and paste the times from the report, it doesn't seem to want to take the cell format? I did format the cells. I tried to cut/paste into the cell and it didn't work, but if I typed it in, it converted it. This is very frustrating. I've also been looking to see if maybe there is a function that will take the first five characters on the left of the cell or possilby even deleting the right three character in the cell to isolate the time without the AM/PM suffix. Jason "Kevin B" wrote: Format the cell you enter the time into as a 24 hour clock format. You can enter 4:33 pm and the format will modify the display to 16:33:00, but you cannot enter 16:33 pm as it's not a valid time entry. -- Kevin Backmann "jbly" wrote: Is there a way to calculate without having to go through and take out the AM and PM? It's no difference that what I'm doing now. I enter the data in as 24 hour format manually. If I delete the PM, then it doesn't know to add 12 hours to the data. I want to have it convert that 4:43 PM to a 24 hour format automatically. Jason "Kevin B" wrote: Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
I'm just selecting the text off the report and copy/pasting into Excel. I'll
try to extract text. Jason "Kevin B" wrote: Is your report a text file? If so you can use the import wizard in Excel to import the data into a workbook. Click FILE in the menu and select OPEN. In the OPEN FILE dialog box change the FILES OF TYPE to text files, locate the report file and click OPEN. This automatically activates the import wizard where you can tell Excel how to parse the data in the report. as for extracting text you can reference the following functions in HELP: LEFT RIGHT MID -- Kevin Backmann "jbly" wrote: Why is that when I cut and paste the times from the report, it doesn't seem to want to take the cell format? I did format the cells. I tried to cut/paste into the cell and it didn't work, but if I typed it in, it converted it. This is very frustrating. I've also been looking to see if maybe there is a function that will take the first five characters on the left of the cell or possilby even deleting the right three character in the cell to isolate the time without the AM/PM suffix. Jason "Kevin B" wrote: Format the cell you enter the time into as a 24 hour clock format. You can enter 4:33 pm and the format will modify the display to 16:33:00, but you cannot enter 16:33 pm as it's not a valid time entry. -- Kevin Backmann "jbly" wrote: Is there a way to calculate without having to go through and take out the AM and PM? It's no difference that what I'm doing now. I enter the data in as 24 hour format manually. If I delete the PM, then it doesn't know to add 12 hours to the data. I want to have it convert that 4:43 PM to a 24 hour format automatically. Jason "Kevin B" wrote: Omit the am/pm suffix since your using a 24 hour clock. 16:43 can only be a pm entry so your pm suffix is what's causing the #VALUE error. -- Kevin Backmann "jbly" wrote: I'm still not getting it. It still has the same value and is giving #VALUE! in the calculation. Jason "Kevin B" wrote: Use the calculations that you indicated (A2-A1) but format the cell using the following Time format: [h]:mm:ss Select the result cell, click FORMAT in the menu and select CELLS. In the FORMAT CELLS dialog box locate the CUSTOM category in the category pane and then locate the above format in the TYPE: list box. You can delete the ':ss' if you do not want to see the seconds displayed. -- Kevin Backmann "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552 hours
beyond what you wanted? (Though you may be fooled as to what answer you've got if you format as h:mm rather than as [h]:mm, and therefore don't display the number of whole days.) Another way is =MOD(A2-A1,1) -- David Biddulph "Warren Easton" wrote in message ... Try =IF(A2A1,(A2-A1),(A2+24)-A1) -- Regards Warren "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
Hi David,
This formula was for day shifts and night shifts the +24 was for going through 12 midnight, the +1 may also work but I used the +24, it was a while ago. -- Regards Warren "David Biddulph" wrote: Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552 hours beyond what you wanted? (Though you may be fooled as to what answer you've got if you format as h:mm rather than as [h]:mm, and therefore don't display the number of whole days.) Another way is =MOD(A2-A1,1) -- David Biddulph "Warren Easton" wrote in message ... Try =IF(A2A1,(A2-A1),(A2+24)-A1) -- Regards Warren "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
You need to remember that Excel stores times in days, not hours, so +1 adds
1 day to cope with going through midnight, +24 would add 24 days, and therefore would give you an answer in error by 552 hours. -- David Biddulph "Warren Easton" wrote in message ... Hi David, This formula was for day shifts and night shifts the +24 was for going through 12 midnight, the +1 may also work but I used the +24, it was a while ago. -- Regards Warren "David Biddulph" wrote: Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552 hours beyond what you wanted? (Though you may be fooled as to what answer you've got if you format as h:mm rather than as [h]:mm, and therefore don't display the number of whole days.) Another way is =MOD(A2-A1,1) -- David Biddulph "Warren Easton" wrote in message ... Try =IF(A2A1,(A2-A1),(A2+24)-A1) -- Regards Warren "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time difference
You're right, Thanks.
-- Regards Warren "David Biddulph" wrote: You need to remember that Excel stores times in days, not hours, so +1 adds 1 day to cope with going through midnight, +24 would add 24 days, and therefore would give you an answer in error by 552 hours. -- David Biddulph "Warren Easton" wrote in message ... Hi David, This formula was for day shifts and night shifts the +24 was for going through 12 midnight, the +1 may also work but I used the +24, it was a while ago. -- Regards Warren "David Biddulph" wrote: Why +24? Wouldn't +1 be enough? Wouldn't +24 give you an answer 552 hours beyond what you wanted? (Though you may be fooled as to what answer you've got if you format as h:mm rather than as [h]:mm, and therefore don't display the number of whole days.) Another way is =MOD(A2-A1,1) -- David Biddulph "Warren Easton" wrote in message ... Try =IF(A2A1,(A2-A1),(A2+24)-A1) -- Regards Warren "jbly" wrote: I can't seem to find it in the help. How do I calculate time difference? We have a network in/out board. I get a report of when I clocked in and out for the day. I want to paste this into Excel and have it calculate how much time I worked. It comes in as: A1 07:38 AM A2 10:55 AM A3 12:01 AM A4 16:43 PM I want it to calculate A2-A1 and A4-A3. Please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Difference in Time (Amt. of Time Transpired) | Excel Discussion (Misc queries) | |||
calculating time difference | Excel Worksheet Functions | |||
calculating difference in time | Excel Discussion (Misc queries) | |||
Calculating time difference over midnight! | Excel Discussion (Misc queries) | |||
Calculating time difference | Excel Discussion (Misc queries) |