Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
I am trying to figure out a formula to use to show if a project is completed
on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
And how could it be displayed in minutes?
"hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
Try this:
Enter start time in A1 and completed time in B1 (enter hours and minutes seperated by :) In C1 enter formula: =IF(B1A1,TEXT(B1-A1,"h:mm")&" late",TEXT(A1-B1,"h:mm")&" early") Hope this helps "hawaiiham2000" wrote: And how could it be displayed in minutes? "hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
We are getting warmer... but if the due time is 11pm and it is completed at 1
am, we want that to be 2 hrs late, not 22 hrs early. Do we have to include a date? "Ron@Buy" wrote: Try this: Enter start time in A1 and completed time in B1 (enter hours and minutes seperated by :) In C1 enter formula: =IF(B1A1,TEXT(B1-A1,"h:mm")&" late",TEXT(A1-B1,"h:mm")&" early") Hope this helps "hawaiiham2000" wrote: And how could it be displayed in minutes? "hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
But why isn't it early? How does excel know you want it to be 2hrs late? A
simple solution would be to enter the date and time: Format the time input cells to dd/mm/yy hh:mm. But be aware the formula will only calculate up to 23 hours 59 minutes. "hawaiiham2000" wrote: We are getting warmer... but if the due time is 11pm and it is completed at 1 am, we want that to be 2 hrs late, not 22 hrs early. Do we have to include a date? "Ron@Buy" wrote: Try this: Enter start time in A1 and completed time in B1 (enter hours and minutes seperated by :) In C1 enter formula: =IF(B1A1,TEXT(B1-A1,"h:mm")&" late",TEXT(A1-B1,"h:mm")&" early") Hope this helps "hawaiiham2000" wrote: And how could it be displayed in minutes? "hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
Thanks so much! It shouldn't be later than 23 hrs. 59 min. so we're on the
right track. How could we convert it so it shows only minutes and have late in negative minutes so that they can be added and averaged? Also, I formatted the cells in the format you recommended (dd/mm/yy hh:mm) but what would be an easy way to enter the data? Thanks for your patience! "Ron@Buy" wrote: But why isn't it early? How does excel know you want it to be 2hrs late? A simple solution would be to enter the date and time: Format the time input cells to dd/mm/yy hh:mm. But be aware the formula will only calculate up to 23 hours 59 minutes. "hawaiiham2000" wrote: We are getting warmer... but if the due time is 11pm and it is completed at 1 am, we want that to be 2 hrs late, not 22 hrs early. Do we have to include a date? "Ron@Buy" wrote: Try this: Enter start time in A1 and completed time in B1 (enter hours and minutes seperated by :) In C1 enter formula: =IF(B1A1,TEXT(B1-A1,"h:mm")&" late",TEXT(A1-B1,"h:mm")&" early") Hope this helps "hawaiiham2000" wrote: And how could it be displayed in minutes? "hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
If you don't include a date, how would you want to decide whether it's 2
hours late or 22 hours early? Do you want to say that it can't be 12 hours or more early or late? -- David Biddulph "hawaiiham2000" wrote in message ... We are getting warmer... but if the due time is 11pm and it is completed at 1 am, we want that to be 2 hrs late, not 22 hrs early. Do we have to include a date? "Ron@Buy" wrote: Try this: Enter start time in A1 and completed time in B1 (enter hours and minutes seperated by :) In C1 enter formula: =IF(B1A1,TEXT(B1-A1,"h:mm")&" late",TEXT(A1-B1,"h:mm")&" early") Hope this helps "hawaiiham2000" wrote: And how could it be displayed in minutes? "hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Completed
You're right. I think a date has to be included.
This is the scenario: The deadline is 11 p.m. each night. We want to figure the out the total minutes late or early for each project and eventually then be able to Sum those and come up with an average. "David Biddulph" wrote: If you don't include a date, how would you want to decide whether it's 2 hours late or 22 hours early? Do you want to say that it can't be 12 hours or more early or late? -- David Biddulph "hawaiiham2000" wrote in message ... We are getting warmer... but if the due time is 11pm and it is completed at 1 am, we want that to be 2 hrs late, not 22 hrs early. Do we have to include a date? "Ron@Buy" wrote: Try this: Enter start time in A1 and completed time in B1 (enter hours and minutes seperated by :) In C1 enter formula: =IF(B1A1,TEXT(B1-A1,"h:mm")&" late",TEXT(A1-B1,"h:mm")&" early") Hope this helps "hawaiiham2000" wrote: And how could it be displayed in minutes? "hawaiiham2000" wrote: I am trying to figure out a formula to use to show if a project is completed on time. If the due time is 11pm and the project is completed at 9pm it could show that it was on time by 2 hours; and if the project is not completed until 1am, how could I show that it was 2 hours late? Any help would be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the last completed row | Excel Discussion (Misc queries) | |||
If A1=X cell d1 must be completed before row 2 can be used | Excel Worksheet Functions | |||
Past Due/Completed on time date formula | Excel Discussion (Misc queries) | |||
IF completed THEN invisible | Excel Discussion (Misc queries) | |||
Number of tasks completed in a specific time frame | Excel Discussion (Misc queries) |