Converting a negative value into positive using IF/any other function

[Latha]

hi,

i have 2 columns with start time and end time. i found the differece between them and found there is some negative timings.
Hence, i would like to convert them into positive.

Example may be easier to understand :

<TABLE style="WIDTH: 462pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=615><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=77>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=92>Start time (hrs)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=122>End time (hrs)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=155>Time spent in office</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 127pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=169>answer required (hrs)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>xyz</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>22:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>6:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>-0.667</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>8</TD></TR></TBODY></TABLE>

plz help

Regards,
Latha

 

[Latha]

plz

 

[Mike Blackman]

Hi,

ABS Function turns a negative into a positive.

=ABS(A1)

 

[Latha]

thanks for the reply. ofcourse, ABS converts negative to positive.

but when comes to time and date, ABS gives the false data

<TABLE style="WIDTH: 345pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=460><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=68>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 110pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=146>StartDate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=99>StartTime</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>EndDate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=83>EndTime</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>xyz</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>22:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>3/2/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>6:00</TD></TR></TBODY></TABLE>

In the above table, Start date and end date are different. and imagine, an employee start his work at 22:00 hrs on 3/1/2011 and ends at 6:00 hrs on 3/2/2011(m/d/yyyy).

so, hours spent in office should be 8 hrs. but ABS function doesnot give the proper answer.

Please guide me

 

[Mike Blackman]

Which is entirely why you should post your whole problem not half of it.

1 sec.

 

[Mike Blackman]

How about;

=(D2+E2)-(B2+C2)

 

[ogo]

If the endtime is numerically less than starttime then actual time will be 24-starttime + endtime. I believe it will be easy to write the code.

Thanks,

Ogo

 

[Latha]

Hi all,

Thanks for the support and suggestions.

I found a function for the below issue by trail and error method. (i also wanted to show the time spent in office in minutes)

formula is

=HOUR(ABS(CONCATENATE(D2,E2)-CONCATENATE(B2,C2)))*60+MINUTE(ABS(CONCATENATE(D2,E2)-CONCATENATE(B2,C2)))

the above formula gives the time in minutes format i.e., 480 minutes.

 

[Latha]

Regards,
Latha

 

[Mike Blackman]

Hi,

This does the same thing;

=ABS(((D2+E2)-(B2+C2))*1440)

And I can't see in your post where you requested it in minutes, if you actually want it in hours then;

=ABS(((D2+E2)-(B2+C2))*24)