how to calculate half day in excel formula

[webmax]

Hi,
I have a data with three columns
1) Start date
2) End Date
3) No of Days

I want to calculate the date between end date and start date using the excel formula.

My concern is if the start date and end date is half date ie 01-01-2023 12:00:00 PM then the no of days to come as 0.5 but it is coming as 1.5 day.

Need excel formula for calculating half day

Thanks
Webmax

[sandy666]

From Date	To date	No of Days	Subtraction
01/01/2023 00:00	03/01/2023 00:00	3	2
01/01/2023 00:00	01/01/2023 12:00	1.5	0.5

with Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"From Date", type datetime}, {"To date", type datetime}}),
    Subtract = Table.AddColumn(DT, "Subtraction", each [To date] - [From Date], type duration),
    Dec = Table.TransformColumnTypes(Subtract,{{"Subtraction", type number}})
in
    Dec

with formula maybe try subtract time not days

or
to date - from date -1

[webmax]

Thanks for sharing. But my requirement is in the first sample if the start date is 1/1/2023 and end date is 3/1/2023 then it should come as 3 days.

[JohnTopley]

Try

=B2-A2+IF(INT(B2)=INT(A2),0,1)

[webmax]

If from date and to date is same date then the no of days should come as 1 but it is come as zero

[JohnTopley]

Try

=(INT(B2)-INT(A2))+MOD(B2,1)+1

[sandy666]

end date is not over yet so it can't be treated as full ended day that is why I said try subtract time not days

[sandy666]

subtraction of the same dates = 0 not 1
1-1 = 1 or 0 ? basic math

[webmax]

still i am getting wrong dates using the above formula.

Attaching the excel sheet with different scenarios. i have also added the output required.

Thanks
Webmax

[JohnTopley]

See attached

[JohnTopley]

Try

=(INT(B6)-INT(A6))-MOD(B6,1)-MOD(A6,1)+1

allows for midday START