Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Time Formula in Excel 1

Status
Not open for further replies.

GFAlexander

IS-IT--Management
Joined
Nov 12, 2001
Messages
68
Location
GB
I need to achieve the following in Excel:

3 columns called Service Time, Arrival Time, Variance
The cells are forattedas time - 13:30 and here is what I want the formula to do

Example 1
Service Time Arrival Time Variance
12:00 13:00 1:00

Example 2
Service Time Arrival Time Variance
12:00 11:00 -1:00

Can you help?

Thanx
 
Question is, what will you do with the data next?

The easy answer is put a formula in the 3rd column, something like =sum(a2-a1)

Where a2 is the arrival time and a1 is the service time.

The problem you'll have is that Excel (as far as I know) won't show a negative time. You will only see ######## in the column.

This is only a display problem - it is quite happy to work with a negative value, as in fact it doesn't work with time as you or I do, it works with 'fractions' of a whole day.

There are several fairly straighforward solutions;

You could show a positive time using an ABS formula, with a minus sign in the next column based on an IF formula. You can still use the original data to make sums etc (don't of course use the ABS data, it will be wrong).

You could show the value rather than the time format (-1:00 would appear as -0.0416666 recurring, as it is 1/24th of one day).

Conditionally format the cell to show negatives in a different colour.

Perhaps if you describe a little more the whole sheet you are making, and if you want to your level of expertise, we can make more specific suggestions.
 
if service time is in col A
and arrival time in col B

then

in col C


=IF(A1>B1,"-"&T(TEXT(ABS((VALUE(A1)-VALUE(B1))),"hh:mm")),T(TEXT(ABS((VALUE(A1)-VALUE(B1))),"hh:mm")))
 
Oh..and format col C as general :-)
 
If your only dealing with hours, how about amending the format of the cells in the c column, try using the standard formula (=a2-a1) and change the c columns cell format to [hh]":00"
 
ETID, bless your heart. I'm working on a Time In, Time Out situation myself and your formula works great. Thank you.
 
Thanks...gald it works for you all :-)
 
One more thing ETID if i can prevail.

If I have one variance for each day of the week using your formula, can I then do a sum on the 7 variences and and get an overall total as follows:

Monday 01:00
Tuesday 00:15
Wednesday 00:30
Thursday -00:45
Friday -01:00
Saturday 00:30
Sunday 00:00

Total 00:30

Thanx again, you're a real lifesaver
 
I hope this lines up in the edit window correctly :-)


The only way I know is to add another column,..in this case C ....(you may want to hide it so as not to confuse users)
then the Total in col B will reference col C



A B C
Monday 01:00 =IF(ISERROR(VALUE(B1)),VALUE(RIGHT(B1,5))*-1,VALUE(B1))
Tuesday 00:15 =IF(ISERROR(VALUE(B2)),VALUE(RIGHT(B2,5))*-1,VALUE(B2))
Wednesday 00:30 =IF(ISERROR(VALUE(B3)),VALUE(RIGHT(B3,5))*-1,VALUE(B3))
Thursday -00:45 =IF(ISERROR(VALUE(B4)),VALUE(RIGHT(B4,5))*-1,VALUE(B4))
Friday -01:00 =IF(ISERROR(VALUE(B5)),VALUE(RIGHT(B5,5))*-1,VALUE(B5))
Saturday 00:30 =IF(ISERROR(VALUE(B6)),VALUE(RIGHT(B6,5))*-1,VALUE(B6))
Sunday 00:00 =IF(ISERROR(VALUE(B7)),VALUE(RIGHT(B7,5))*-1,VALUE(B7))

Total =IF(SUM(C1:C7)<0,&quot;-&quot;&TEXT(ABS(SUM(C1:C7)),&quot;hh:mm&quot;),TEXT(SUM(C1:C7),&quot;hh:mm&quot;))
 
Thanx again ETID,

That should be all now, and works a traet.

Gary
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top