Count Days Between Two Dates

Capsaicin Burn

New Member
Joined
Jan 26, 2018
Messages
38
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
Formula in C1: =B1-A1
Formula in D1: =NETWORKDAYS(A1,B1)
 

Capsaicin Burn

New Member
Joined
Jan 26, 2018
Messages
38
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
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))
 
Last edited:

Capsaicin Burn

New Member
Joined
Jan 26, 2018
Messages
38
A1 is FALSE and B1 is TRUE. Will any date format do? I tried a couple and nothing changed
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
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

New Member
Joined
Jan 26, 2018
Messages
38
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,077,662
Messages
5,335,561
Members
399,024
Latest member
rokcel389

Some videos you may like

This Week's Hot Topics

Top