Count Days Between Two Dates

[Capsaicin Burn]

I thought this would be simple but my attempts are not working for me. I need two things PLEASE.

I am trying to calculate the total number of "days" between two dates, and secondly, calculate the number of "week days" between the same dates. Each would be in a separate cell. I am totally clueless on the Week Day formula.

I am using Office 365

I have:
A1 = Start Date
B1 = End Date
C1 = Total Days Results
D1 = Week Days Results

I have tried several formulas.
I tried =DATEDIF(A1,B1,"d") also tried switching the A1 and B1.
I tried =DAYS(A1,B1) also switching A1 and B1.
Then I tried =DATEDIF(A1,B1,C1) with adding an additional column with simply a D in C1.

None of these work for me. All of them result in #VALUE !

Any help would be greatly appreciated!

Jim

 

[Joe4]

Formula in C1: =B1-A1
Formula in D1: =NETWORKDAYS(A1,B1)

 

[Capsaicin Burn]

Both of those result in the Value error also. I checked formatting and it is set to "General". Is there anything else that may cause these formulas to fail?

 

[Joe4]

Sounds like your dates aren't actually entered as dates, but rather as text.
What do these formulas return?
=ISNUMBER(A1)
=ISNUMBER(B1)

If they return FALSE, then you have text entries and not valid date entries.
Please exactly what the dates look like, so we can come up with a way to convert them to dates.

It may be as easy as using DATEVALUE, so the formulas would look like:
Formula in C1: =DATEVALUE(B1)-DATEVALUE(A1)
Formula in D1: =NETWORKDAYS(DATEVALUE(A1),DATEVALUE(B1))

 

[Capsaicin Burn]

A1 is FALSE and B1 is TRUE. Will any date format do? I tried a couple and nothing changed

 

[Joe4]

A1 is FALSE and B1 is TRUE.

That means that A1 is not a valid date entry, it is a Text entry.

Will any date format do? I tried a couple and nothing changed

If the value of the date was entered as a date, the format makes no difference. Trying to change the Formatting on a Text entry has no impact on it (Formatting only works on numeric entries).
If it was entered as text, we need to know EXACTLY what the value looks like so we know how to convert it!

So please show us EXACTLY what the entries in A1 and B1 look like.

 

[Capsaicin Burn]

Ok, formatting was the issue but not with the cell. The date text was entered incorrectly. Your formulas worked great not that the text is fixed. Thank you very much for your help.

 

[Joe4]

You are welcome.

I would highly recommend using Data Validation on cells A1 and B1 to only allow valid Date entries to be made.
If you do not, it will not allow them to make any text entries.

 

[Capsaicin Burn]

Is data validation done by using =ISNUMBER(A1) as an example?

 

[Joe4]

No, do the following:
1. Select cells A1 and B1
2. Go to the Data menu
3. From the "Data Tools" ribbon, select "Data Validation"
4. In the "Allow" box, select "Date"
5. Enter the minimum allowable date in the "Start Date", and the maximum allowable date in the "End Date" box
6. You can enter "Input Messages" and "Error Alerts", if you like, on the other tabs, but it is not necessary.
7. Click OK

Now watch what happens when you try to enter anything that isn't a date in those cells.