Counting days between two dates which a day is 7.5 hours

[lacombeg]

Hello,
I am looking for a formula that would count the days between two dates, start and end. My day counts as 7.5 hours, therefore if I put in July 18 as a start date and July 18 as the end date, I would like the result to be 1 day and not 0.
Here is a copy of my spreadsheet.

This is my formula in column B: =WORKDAY(A5,C5)
When inserting amount of days, it automatically inserts the end date

This is my formula in column D: =IF(TODAY()<A5,"",MIN(1,(TODAY()-A5)/C5))

This is my formula in column E: =IF(D5="", "Not started", IF(D5<100%, "In progress", "Completed"))

Column A
Start Date
27-Apr-16
27-Apr-16
2-May-16
9-May-16
11-May-16

Column B
End Date
15-May-16
2-May-16
9-May-16
10-May-16
18-May-16

Column C
Number of Days
14
3
5
1
5

Column D
Percentage Completed
75%
100%
100%
100%
0%

Column E
Status
In progress
Completed
Completed
Completed
In progress

Thanks

 

[pbornemeier]

IF the column C formula is =NETWORKDAYS(A5,B5)+1 then the same date in Columns' A & B would return 1 day.

 

[lacombeg]

IF the column C formula is =NETWORKDAYS(A5,B5)+1 then the same date in Columns' A & B would return 1 day.

Thanks for your reply

Because I already have a formula in Column B, when I enter the suggested formula in column C, it give me a Circular Reference Warning.

 

[wideboydixon]

Could you re-post your formula for Column D and be sure to put a single space around < and > characters?

WBD

 

[lacombeg]

Of course, here it is
=IF(TODAY()<A5,"",MIN(1,(TODAY()-A5)/C5))

 

[lacombeg]

I will try it by sending part of it in several posts

<A5,"",MIN(1,

I have been trying to post my formula but it cuts it off

Of course, here it is
=IF(TODAY()<A5,"",MIN(1,(TODAY()-A5)/C5))

 

[lacombeg]

Here is part two:


<A5,"",MIN(1,

I have been trying to post my formula but it cuts it off[/QUOTE]

 

[wideboydixon]

Put spaces around the < and > signs or you will just confuse the BB code ...

WBD

 

[lacombeg]

=if(today() < a5,"",min(1,(today()-a5)/c5))

 

[wideboydixon]

This?

Book1
	A	B	C	D	E
1	Start Date	End Date	Number of Days	Percentage Completed	Status
2	11-Jul-16	29-Jul-16	14	14.29%	In progress
3	11-Jul-16	14-Jul-16	3	66.67%	In progress
4	14-Jul-16	21-Jul-16	5		Not started
5	11-Jul-16	12-Jul-16	1	100.00%	Completed
6	11-Jul-16	18-Jul-16	5	40.00%	In progress
Sheet1

Cell Formulas
Range	Formula
D2	=IF(TODAY()1,(NETWORKDAYS(A2,TODAY())/C2)))
E2	=IF(D2="","Not started",IF(D2<1,"In progress","Completed"))
B2	=WORKDAY(A2,C2)

WBD