Pulling just the hh:mm from a cell with mm/dd hh:mm

[calvin771]

I am trying to pull just the date out of a field for comparison's sake.

I am capturing the time of an event using the NOW() function. This is driven by a drop-down menu that populates a start time and finish time field. It gives me the timestamp in mm/dd hh:mm format. I need to pull just the time out of that information.

For example, the trouble I'm having is this:

8:46 AM = 0.3656

8/4/09 8:46 AM = 40028.3656

I need the 0.3656 to compare in a nested IF statement. The 40028 portion is causing me difficulties.

I understand that the 40028 is the number of complete days since Excel starts counting time, but I can not think of a clean way to pull that information out.

Ultimately, I have a nested IF statement that will select which group a project was started and finished by depending on the time of day. I just need help stripping the date from the original timestamp and leaving a number that can be used in a nested IF statement.

 

[jim may]

=a1-int(a1)

 

[calvin771]

I'll give that a shot. If it doesn't work, I'll post the IF statement I'm trying to use.

 

[DonkeyOte]

the other alternative to Jim's approach is to use MOD, ie: =MOD(A1,1)

 

[calvin771]

Jim's approach worked perfectly.

Thank you very much!