Dropping the date from cell

[bethieb]

I have a spreadsheet sent from another company. The cell has the date first and then the time. My formula figures the time only so I need to drop the date. I tried to change the cell but it won't change. Any suggestions. The spreadsheet used to have the time only and I would convert it to am/pm. My formula doesn't work with the new setup. Thanks.

 

[DonkeyOte]

Change your formulas to use the MOD value in the cell

=MOD(A1,1) would return just time portion of A1 where A1 = date / time

 

[bethieb]

Ok, I must explain I am not very great at this. Here is what I have
<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=232 border=0 x:str><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>30JUN08:17:43:38</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>01JUL08:06:16:00


</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64 x:fmla='=TEXT(IF(B1<A1,1-A1+B1,B1-A1),"hh.mm")' x:err="#VALUE!"></TD></TR></TBODY></TABLE>Above is the in time and out time



=TEXT(IF(F10<E10,1-E10+F10,F10-E10),"hh.mm")
I used to convert these times to am/pm.

Sorry if I am confusing you. I would really like to change the times and not the formulas because using military time is difficult for the girls in the office. Thanks.

 

[Norie]

Those aren't real dates/times they are text.

So I'm afraid lasw10's formula won't work.

If the time is always the last 8 characters try this and format as required.

=TIMEVALUE(RIGHT(A1,8))

 

[bethieb]

Shoot it isn't working.

 

[bethieb]

It is saying it is a circular reference. It is returning as midnight every time.

 

[DonkeyOte]

can you paste up the 2 date values (just as text no need to insert image or anything) and also the formula you are trying to use which is generating a circular reference (this should not be)

 

[bethieb]

<TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=137 border=0 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 103pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=137 height=17>05JUL08:04:49:36</TD></TR></TBODY></TABLE>
That is the date, I am using =TIMEVALUE(RIGHT(E4,8))

It comes back as 12:00am and says it is a circular reference.

 

[DonkeyOte]

Works ok for me -- in which cell is the date and in which cell are you putting the formula ?

the "E4" reference in your formula should reflect the cell that contains the date entry in your data.

 

[bethieb]

It is the cell but will this not return it in the same cell? I wanted it to change in the same cell. Is that not possible or am I confused?