Automating Dates

[mmode66]

My "Days Worked" column keeps breaking with every new month... and i think i have it coded wrong.
My formula is =NETWORKDAYS($C$7,$C$8)*-1-$C$11

C7 is Today / C8 is Start Date (beginning of the month) / C11 is Holidays (which i manually enter due to company picks)..

Currently it's showing zero where it should show 1.

Anyone have ideas on how i could code that better so it works each month?

 

[Joe4]

It doesn't appear that you have set-up the formula correctly.

NETWORKDAYS has three arguments, i.e.
NETWORKDAYS(start date, end date, holidays)

It appears that maybe you have the dates reversed (shouldn't C8 be the start date?) and you don't have the holidays in the formula.
See here for examples: https://www.techonthenet.com/excel/formulas/networkdays.php

If you still cannot get it to work, please let us know the exact values you have in C7, C8, and C11.

 

[mmode66]

Tried that and now getting 22 days worked.
C8 (Start Date) is no formula (just entered 11/1/2018
C9 (End Date) is no formula (just entered 11/30/2018
C11 (Holidays) is also manual. Only Today has a formula (=TODAY()) in C7
E11 (Days worked) I changed to what you suggested (=NETWORKDAYS($C$8,$C$9,$C$11)) but it now shows 22

 

[Joe4]

C8 (Start Date) is no formula (just entered 11/1/2018
C9 (End Date) is no formula (just entered 11/30/2018
C11 (Holidays) is also manual. Only Today has a formula (=TODAY()) in C7
E11 (Days worked) I changed to what you suggested (=NETWORKDAYS($C$8,$C$9,$C$11)) but it now shows 22

I just entered those dates in those cells, and that formula in E11, and it correctly returns 22 for me.
What happens if you remove the value from C11 (and leave it blank)? Does your value change?

 

[mmode66]

It should show 1 day worked, and remaining days 19.
Basically I want to enter the start and end dates for the month, then have days worked and days remaining auto fill throughout the month.
I have several formulas that use those dates to countif, sumif and average out for multiple stats on the month.
I have your link up you provided and i've tried using the serial numbers but not sure I'm getting it.

My layout:
C7: Today's date (=TODAY())
C8: Start Date (=AA10 which points to a cell showing: 43405)
C9: End Date (=AA11 which points to a cell showing: 43434)
C10: Working Days (=NETWORKDAYS($C$8,$C$9)-$C$11)
C11: Holidays (i filled in 2, however i took your link suggestion and tried making two separate cells with dates for my days worked field (AA7:AA8))
E11: Days Worked (=NETWORKDAYS($AA$10,$AA$11,AA7:AA8)
G11: Remaining Days (=$C$10-$E$11)

 

[Joe4]

You didn't answer the question I posted in my previous post.
Based on the example you posted up in reply number 3, you said it was returning 22 for you.
I asked you what happens if you delete the value entered in cell C11. When you do this, does the formula in E11 still return 22, or does that change.
(By the way, is it safe to assume that you have Calculation mode set to Automatic and not Manual?)

 

[mmode66]

E11 should return 1, not 22 (Days worked in November is 1). Sorry, i should have broke that out and addressed it separately.

Your formula in the beginning fixed my Working Days perfectly (C10).
Somehow i need to have a formula look at todays date - days in the month - holidays (if present) and give me a total days worked (and days remaining)

 

[Joe4]

OK, this all seems to work for me:

Formula for Working Days (C10):

=NETWORKDAYS($C$8,$C$9,$AA$7:$AA$8)

Formula for Days Worked (E11):

=NETWORKDAYS($C$8,$C$7,$AA$7:$AA$8)

Formula for Remaining Days (G11):

=$C$10-$E$11

 

[mmode66]

That worked!
I guess i had the two sections wrong which was throwing mine off.
You rock!

 

[Joe4]

You are welcome!

One other problems was this formula:

[COLOR=#333333]=NETWORKDAYS($C$8,$C$9)-$C$11[/COLOR]

The holidays (C11) need to be in the NETWORKDAYS function, not outside it.