Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with dates and time
Hi,
Is it possible for a formula to calculate the diffence between seperate cells.For example can A&B & D&E be read as one.The answer i'm looking for in F is LATE BY 1day 2hr. If they match to read ON-TIME, and if early to read EARLY BY and enter time Expected Actual Answer A B D E F 05/03/2006 12:00 06/03/2006 14:00 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with dates and time
=IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+D1,"Early by "&TEXT(A1+B1-(C1+D1),"d
""days ""hh:mm"),"Late by "&TEXT(C1+D1-(A1+B1),"d ""days ""hh:mm"))) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Hi, Is it possible for a formula to calculate the diffence between seperate cells.For example can A&B & D&E be read as one.The answer i'm looking for in F is LATE BY 1day 2hr. If they match to read ON-TIME, and if early to read EARLY BY and enter time Expected Actual Answer A B D E F 05/03/2006 12:00 06/03/2006 14:00 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with dates and time
Thanks Bob
the formula works great,is it possible for when an answer is displayed the text colour can change for example Late - would be red Early - would be blue On-time - would be green Thanks Ben "Bob Phillips" wrote: =IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+D1,"Early by "&TEXT(A1+B1-(C1+D1),"d ""days ""hh:mm"),"Late by "&TEXT(C1+D1-(A1+B1),"d ""days ""hh:mm"))) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Hi, Is it possible for a formula to calculate the diffence between seperate cells.For example can A&B & D&E be read as one.The answer i'm looking for in F is LATE BY 1day 2hr. If they match to read ON-TIME, and if early to read EARLY BY and enter time Expected Actual Answer A B D E F 05/03/2006 12:00 06/03/2006 14:00 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with dates and time
You would do that with conditional formatting, FormatConditional
Formatting. Set the condition to equal, and the value to the text, and format using a colour from the Palette tab. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Thanks Bob the formula works great,is it possible for when an answer is displayed the text colour can change for example Late - would be red Early - would be blue On-time - would be green Thanks Ben "Bob Phillips" wrote: =IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+D1,"Early by "&TEXT(A1+B1-(C1+D1),"d ""days ""hh:mm"),"Late by "&TEXT(C1+D1-(A1+B1),"d ""days ""hh:mm"))) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Hi, Is it possible for a formula to calculate the diffence between seperate cells.For example can A&B & D&E be read as one.The answer i'm looking for in F is LATE BY 1day 2hr. If they match to read ON-TIME, and if early to read EARLY BY and enter time Expected Actual Answer A B D E F 05/03/2006 12:00 06/03/2006 14:00 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with dates and time
Just realised, that won't work. You do use CF, but change Condition 1 to
Formula Is, and use a formula of =LEFT(E1,4)="Early" assuming that the formulae start in E1. Then add other conditions with formulae of =LEFT(E1,5)="Early" and =LEFT(E1,7)="On Time" -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Thanks Bob the formula works great,is it possible for when an answer is displayed the text colour can change for example Late - would be red Early - would be blue On-time - would be green Thanks Ben "Bob Phillips" wrote: =IF(A1+B1=C1+D1,"On Time",IF(A1+B1C1+D1,"Early by "&TEXT(A1+B1-(C1+D1),"d ""days ""hh:mm"),"Late by "&TEXT(C1+D1-(A1+B1),"d ""days ""hh:mm"))) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Hi, Is it possible for a formula to calculate the diffence between seperate cells.For example can A&B & D&E be read as one.The answer i'm looking for in F is LATE BY 1day 2hr. If they match to read ON-TIME, and if early to read EARLY BY and enter time Expected Actual Answer A B D E F 05/03/2006 12:00 06/03/2006 14:00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the working days difference between to dates? | Excel Discussion (Misc queries) | |||
Continuous dates and time | Charts and Charting in Excel | |||
calculate elapsed time between dates and times | Excel Worksheet Functions | |||
How to create command button to save the dates and time | Excel Discussion (Misc queries) | |||
how to calulate time in hours cbetween two different dates? | Excel Worksheet Functions |