Calculating a Duration as a Fraction when Duplicate Days

datedif
excel calculate elapsed time between two dates
quickbase duration formula
how to calculate days in excel
calculate hours between two times - excel
how to calculate date and time difference in excel
calculate hours between two dates in excel
how to use date and time formula wizard in excel

I have the following test data, that is generated with the following query:

select 
    ID,
    SequenceID,
    [Sub Status Start Date],
    [Sub Status End Date],
    DateDiff(d,[Sub Status Start Date],[Sub Status End Date]) as [Duration Days]
from SampleData

What I'm not sure how to go about is that where there are multiple occurrences of the same Start Date & End Date, to calculate the Duration as a fraction split by the number of duplicates. This is shown in the screenshot where for ID 288, there were 4 duplicates, so it's split the duration in 4 to 0.25 each. For ID 404, there were 3 so it's made them 0.33 each. However, for ID 239, there is only 1 so it remains as 0.

Sample Data

CREATE TABLE [dbo].[SampleData](
    [ID] [int] NOT NULL,
    [SequenceID] [int] NOT NULL,
    [Sub Status Start Date] [datetime] NULL,
    [Sub Status End Date] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 1, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 2, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 3, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 4, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 5, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-13T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 6, CAST(N'2019-08-09T00:00:00.000' AS DateTime), CAST(N'2019-08-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 7, CAST(N'2019-08-13T00:00:00.000' AS DateTime), CAST(N'2019-10-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 8, CAST(N'2019-10-03T00:00:00.000' AS DateTime), CAST(N'2019-10-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 1, CAST(N'2019-11-02T00:00:00.000' AS DateTime), CAST(N'2019-11-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 2, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 3, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 4, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 5, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-29T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 6, CAST(N'2019-11-29T00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 1, CAST(N'2019-06-25T00:00:00.000' AS DateTime), CAST(N'2019-06-25T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 2, CAST(N'2019-06-25T00:00:00.000' AS DateTime), CAST(N'2019-06-26T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 3, CAST(N'2019-06-26T00:00:00.000' AS DateTime), CAST(N'2019-06-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 4, CAST(N'2019-06-28T00:00:00.000' AS DateTime), CAST(N'2019-08-04T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 5, CAST(N'2019-07-01T00:00:00.000' AS DateTime), CAST(N'2019-08-04T00:00:00.000' AS DateTime))
GO

Try This

FIDDLE DEMO

SELECT ID,SequenceID,[Sub Status Start Date],[Sub Status End Date], DATEDIFF(d,[Sub Status Start Date],[Sub Status End Date]) AS [Duration Days],
       CASE WHEN Counts > 1 THEN CAST(1.0 / X.Counts AS DECIMAL(18,2)) 
            WHEN Counts = 1AND X.[Duration Days] > 0 THEN X.[Duration Days]
       ELSE 0 END [What The Result Should Be]
FROM
(
    SELECT ID,SequenceID,[Sub Status Start Date],[Sub Status End Date], DATEDIFF(d,[Sub Status Start Date],[Sub Status End Date]) AS [Duration Days],       
    Counts = COUNT(*) OVER (PARTITION BY ID,[Sub Status Start Date],[Sub Status End Date] ORDER BY ID)
    FROM SampleData
)X ORDER BY ID,SequenceID

Calculate time in Excel: time difference, add, subtract and sum times, As you probably know, times in Excel are usual decimal numbers Another simple technique to calculate the duration between two Display time difference as "XX days, XX hours, XX minutes and XX If your data is written in column F, then you can use the formula to count duplicate hours and minutes. When you enter the amount of hours that an employee has worked in a time sheet, you must enter the minutes as a fraction of an hour. For example, if an employee worked one hour and 20 minutes, you would type 1.333 in the Duration (Hours) field. The following chart shows how minutes are converted to a fraction of an hour by dividing the number


Try this answer.

SELECT D.*
    ,DATEDIFF(DAY,[Sub Status Start Date],[Sub Status End Date]) Duration
    ,CAST(CASE WHEN DATEDIFF(DAY,[Sub Status Start Date],[Sub Status End Date])>0 THEN DATEDIFF(DAY,[Sub Status Start Date],[Sub Status End Date])
      ELSE CASE WHEN A.NoOfEntries = 1 THEN 0 ELSE 1.0/A.NoOfEntries END END AS NUMERIC(8,2)) AS WhatTheResultShouldBe
FROM SampleData D
LEFT JOIN (
  SELECT [ID]
    ,[Sub Status End Date] DateVal
    ,COUNT(1)NoOfEntries
  FROM SampleData
  WHERE [Sub Status Start Date] = [Sub Status End Date]
  GROUP BY [ID],[Sub Status End Date]
    )A ON A.ID = D.ID AND D.[Sub Status End Date] = A.DateVal
          AND DATEDIFF(DAY,[Sub Status Start Date],[Sub Status End Date]) = 0

Output:

|  ID | SequenceID | Sub Status Start Date |  Sub Status End Date | Duration | WhatTheResultShouldBe |
|-----|------------|-----------------------|----------------------|----------|-----------------------|
| 288 |          1 |  2019-08-06T00:00:00Z | 2019-08-06T00:00:00Z |        0 |                  0.25 |
| 288 |          2 |  2019-08-06T00:00:00Z | 2019-08-06T00:00:00Z |        0 |                  0.25 |
| 288 |          3 |  2019-08-06T00:00:00Z | 2019-08-06T00:00:00Z |        0 |                  0.25 |
| 288 |          4 |  2019-08-06T00:00:00Z | 2019-08-06T00:00:00Z |        0 |                  0.25 |
| 288 |          5 |  2019-08-06T00:00:00Z | 2019-08-13T00:00:00Z |        7 |                     7 |
| 288 |          6 |  2019-08-09T00:00:00Z | 2019-08-09T00:00:00Z |        0 |                     0 |
| 288 |          7 |  2019-08-13T00:00:00Z | 2019-10-03T00:00:00Z |       51 |                    51 |
| 288 |          8 |  2019-10-03T00:00:00Z | 2019-10-03T00:00:00Z |        0 |                     0 |
| 404 |          1 |  2019-11-02T00:00:00Z | 2019-11-03T00:00:00Z |        1 |                     1 |
| 404 |          2 |  2019-11-28T00:00:00Z | 2019-11-28T00:00:00Z |        0 |                  0.33 |
| 404 |          3 |  2019-11-28T00:00:00Z | 2019-11-28T00:00:00Z |        0 |                  0.33 |
| 404 |          4 |  2019-11-28T00:00:00Z | 2019-11-28T00:00:00Z |        0 |                  0.33 |
| 404 |          5 |  2019-11-28T00:00:00Z | 2019-11-29T00:00:00Z |        1 |                     1 |
| 404 |          6 |  2019-11-29T00:00:00Z |               (null) |   (null) |                (null) |
| 239 |          1 |  2019-06-25T00:00:00Z | 2019-06-25T00:00:00Z |        0 |                     0 |
| 239 |          2 |  2019-06-25T00:00:00Z | 2019-06-26T00:00:00Z |        1 |                     1 |
| 239 |          3 |  2019-06-26T00:00:00Z | 2019-06-28T00:00:00Z |        2 |                     2 |
| 239 |          4 |  2019-06-28T00:00:00Z | 2019-08-04T00:00:00Z |       37 |                    37 |
| 239 |          5 |  2019-07-01T00:00:00Z | 2019-08-04T00:00:00Z |       34 |                    34 |

SQL Fiddle

Excel DATEDIF, Formula examples demonstrate how to use the Excel DATEDIF function to compare dates and calculate the difference in days, weeks, months� Calculate the number of days, months, or years between two dates using Excel functions. For example, you can calculate age in years, months and days. You can also calculate the number of days between another date and today. You can also calculate elapsed time.


Please try this one..

SELECT s.ID
    ,SequenceID
    ,[Sub Status Start Date]
    ,[Sub Status End Date]
    ,DATEDIFF(DAY, [Sub Status Start Date], [Sub Status End Date]) Duration
    ,CAST(CASE 
            WHEN DATEDIFF(DAY, [Sub Status Start Date], [Sub Status End Date]) > 0
                THEN DATEDIFF(DAY, [Sub Status Start Date], [Sub Status End Date])
            ELSE CASE 
                    WHEN c.NumCount = 1
                        THEN 0
                    ELSE 1.0 / c.NumCount
                    END
            END AS NUMERIC(8, 2)) AS Result
FROM SampleData s
LEFT JOIN (
    SELECT [ID]
        ,[Sub Status End Date] Dated
        ,COUNT(1) NumCount
    FROM SampleData
    WHERE [Sub Status Start Date] = [Sub Status End Date]
    GROUP BY [ID]
        ,[Sub Status End Date]
    ) c ON c.ID = s.ID
    AND s.[Sub Status End Date] = c.Dated
    AND DATEDIFF(DAY, [Sub Status Start Date], [Sub Status End Date]) = 0

Please check result of this query as well.

Formula, Sort the field to find duplicate values, make corrections as necessary, return to the Use the Round formula instead of the decimal places option to round up .5, meaning that -3.5 For example, if you enter "3.5 days," it displays as 302400. Figure A: three-day duration with four hours of work In the Task B example, a Fixed Work task could be entered with a resource assigned 100%, and the resulting duration would be 0.5 days.


Duration in M, A duration value is expressed in Days, Hours, Minutes, and Seconds, and 24 hours, it gives me an error and I can't perform any calculation on it. I've duplicated the Duration column to keep the original but if it's not You can change the time to decimal number type, and then change to duration type. SELECT ID,SequenceID,[Sub Status Start Date],[Sub Status End Date], DATEDIFF(d,[Sub Status Start Date],[Sub Status End Date]) AS [Duration Days], CASE WHEN Counts > 1 THEN CAST(1.0 / X.Counts AS DECIMAL(18,2)) WHEN Counts = 1AND X.[Duration Days] > 0 THEN X.[Duration Days] ELSE 0 END [What The Result Should Be] FROM ( SELECT ID,SequenceID,[Sub Status Start Date],[Sub Status End Date], DATEDIFF(d,[Sub Status Start Date],[Sub Status End Date]) AS [Duration Days], Counts = COUNT(*) OVER


Excel formula: Calculate years between dates - Excelchat, This post will give you an overview of how to Calculate years between dates. two dates and calculate the difference between them in terms of days, weeks, only highlights them as duplicate values if the decimal point is exactly the same. Duration Between Two Dates – Calculates number of days. Time and Date Duration – Calculate duration, with both date and time included; Date Calculator – Add or subtract days, months, years; Birthday Calculator – Find when you are 1 billion seconds old; Related Links. Date/calendar related services – Overview


Chris Webb's BI Blog: Using DateDiff() To Calculate Time Intervals In , It does exactly what you would expect: calculate the amount of time in between two dates, and express that value as either seconds, minutes, hours, days, weeks , A date column containing duplicate dates was specified in the call to Pingback: Dynamic duration calculation using DAX in Power BI and� The Duration Calculator calculates the number of days, months and years between two dates.