1

I am designing a SharePoint data sheet list for recording entry and exit times.

I am using the [Created] column to show the entry time,

a second column called [Out] with a Yes/No choice

and a 3rd calculated column [Time Out] with a formula =IF(Out="Yes",NOW(),"On Site").

This works fine until you add data into other columns then the [Time Out] column keeps on changing time.

How can I make the [Time Out] column only show the time when the [Out] column changes to yes and keep that time, i.e. change from formula to text?

0

Calculated column always calculated value when item was updated.

If you need have value when column [Out] change to yes, you must use event receiver (How to: Create an Event Receiver) and calumn [Time Out] to change the type of calculeted on DataTime.

You can hidden this column in content type, then user can't change it.

You must use Adding and Updating, only sync event you can check change value (BeforeProperties and AfterProperties on SPItemEventReceiver). If column [Out] change to yes put DataTime.Now() in [Time Out].

Example:

/// <summary>
/// List Item Events
/// </summary>
public class EventReceiver1 : SPItemEventReceiver
{
    /// <summary>
    /// An item is being added.
    /// </summary>
    public override void ItemAdding(SPItemEventProperties properties)
    {
        base.ItemAdding(properties);
        if (properties.AfterProperties["Out"] != null && Convert.ToBoolean(properties.AfterProperties["Out"]))
        {
            properties.AfterProperties["TimeOut"] = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now);
        }
    }

    /// <summary>
    /// An item is being updated.
    /// </summary>
    public override void ItemUpdating(SPItemEventProperties properties)
    {
        base.ItemUpdating(properties);
        if (properties.AfterProperties["Out"] == null)
        {
            return;
        }
        if (properties.ListItem["Out"] != properties.AfterProperties["Out"]  && Convert.ToBoolean(properties.AfterProperties["Out"]))
        {
            properties.AfterProperties["TimeOut"] = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now);
        }
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.