Forum Discussion

GuyCarnegie's avatar
GuyCarnegie
Copper Contributor
Jul 13, 2023

Entering UK dates in Sharepoint List and then using DateDif in calc column

I have a column in my SP list for dates. It can only be edited by grid mode as it's not in the edit form - which is fine. I am in UK format dates (dd/MM/yyyy). Note I am using a standard SP list directly on screen - not talking about powerapps here or anything like that.

 

To edit it, I go to grid mode, then select a date using the date picker, which saves dates in US format mm/dd/yyyy. When I try to exit grid mode, it says I have an error and dates should be in UK format.

So I manually type the dates in UK format (I am in the UK so thats fine) and manage to save & exit grid mode.

Now, I have a calculated column which measures the days since that date, which now shows a !value error - likely as it doesn't recognise the UK date format.

Please how can I get the SP online grid-mode Date Picker to use UK dates, and get the DATEDIF function in my calculated field to pick up the correct date from that column?

SharePoint Online

1 Reply

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Jul 13, 2023

    Hi GuyCarnegie,

    you can try these options:

    1. Adjust regional settings:
    - Go to the Site Settings of your SharePoint Online site.
    - Under the "Site administration" (Site settings) section, click on "Regional settings."
    - Choose a locale that matches the UK date format (dd/MM/yyyy).
    - save these changes

    2. Update existing dates:
    - Switch to grid mode in your SharePoint list.
    - Manually edit the existing date values in the UK date format (dd/MM/yyyy).
    - Save the changes.

    3. Modify calculated column formula:
    - Identify the calculated column that uses the DATEDIF function.
    - Update the formula to include the correct reference to the date column and adjust it to handle the UK date format.
    - you can try this formula

    `=DATEDIF([YourDateColumn], Today(), "d")`.

    * Replace `[YourDateColumn]` with the internal or display name of your date column.
    - This formula calculates the number of days between the date in your column and the current date.

    4. Test and validate:
    - Save the changes to the calculated column formula.
    - Verify if the calculated column now displays the expected result, taking into account the updated UK date format.
    - Test with different scenarios to ensure the DATEDIF function handles the date calculations correctly.

     

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

Resources