Formula to calculate dates based off RIGHT CMD

So what I am looking to do in excel is build a calculations date table that works off numbers only, no slash marks, no dashes.

=RIGHT(H188,2)-RIGHT(G188,2)

The purpose of this calculation is to take the date 20171102 and minus it from 20171209 but here is where it gets tricky... I need the calculation to add one more day for every calculation... but if the day falls under a saturday or sunday, i need the data to deduct the date from the calculation... i also need the data to add only days, not by the hundreds but by the calendar dates only. If excel was to do =H188-G188, this would give me negative thirty seven ( -37 ) and I do not want the column to display any negatives period. I also want the ability to add an additional column as additional dates in case the person would want to later change the approved date which would change the days taking off work authorizing a new calculation. for example... IF I188 was 20140102 but H188 is 20131201 and G188 is 20131229 then the days taken would be an additional +3 to include 20140101 but since that is a holiday, the date shouldn't be counted.


Is this possible in an excel spreadsheet? The code what i have above is sufficient but I need help completing it. Thank you.

I didnt understand the next bit "I need the calculation to add one more day for every calculation...

so lets say im taking monday off, thats considered one day even if i return that same day... which excel counts that day as zero....i need excel to count that day as one
and i do not want excel to count the dates as 11/2/2017 because I have a specific formatting for dates which is the year first, then the month and the day.

but if the day falls under a saturday or sunday" what i dont understand is what day are we checking if Saturday or Sunday?
the answer is currently 37 but are you wanting to check thats a weekend day (which obviously isnt possible to check) or one of the original dates?

we're checking both but i had an idea... if we could create a database with all days of saturdays and sundays annotated... could we pull from that database and if the data is true, excel should not
count that day and this would mean 36 (because you said 37, i removed a day which is what exactly i want the code to do); the database should be accessable in a seperate tab which i could access and change
anytime.

I have attached an example book as requested.

You seem to be making life very difficult: you could enter dates in standard format and display as "yyyymmdd" OR leave as you have but convert in Excel format In formulae) and use a function such as NETWORKDAYS.INTL which allows you to count working days and select your non_working days.

I just need the right code to execute the command correctly without an error.

... not sure what you mean by this.

Nor did I really understand what your final outcome should be (sorry!)

nice. Exactly. I think i made a mistake with the dates.

20170330 20170421
20170330 20170501
20170330 20170630

those dates are more accurate in terms of calculating...i have attached the sheet...the code u gave me is in there but there is a value error. thanks.

You have an error as you had 3 formulas in one cell.

This in L8

=NETWORKDAYS.INTL(MIN(DATE(--LEFT(G8:H8,4),--MID(G8:H8,5,2),--RIGHT(G8:H8,2))),MAX(DATE(--LEFT(G8:H8,4),--MID(G8:H8,5,2),--RIGHT(G8:H8,2))),1)

returns 17 as an answer. the others return 23 and 67

Delete the formulae in L8:L10 and re-add the one above in L8:

Enter with Ctrl+Shift+Enter

Copy down.

As previous stated TWICE you must enter the formula with Ctrl+Shift+Enter

Hold down Ctrl+Shift keys then hit Enter


See the attached which has formulae in G2 and H2 to count "lates" without needing add the "helper" columns.

=SUMPRODUCT(--(D2:D7>C2:C7))

and

=SUMPRODUCT(--(E2:E7-D2:D7>1/12))

do NOT set the range to whole columns but use a sensible maximum e,g E2:E5000

the CF is set to whole column as well

sorry if im posting twice... but if i need to create a new thread, please let me know.

I think you need explain more clearly the "pay" logic which (to me) is not obvious from your workbook.

If I take paid leave monday through sunday but im only paid until saturday, then sunday shouldn't count towards my paid vacation days that i have left with the company

Originally Posted by JohnTopley

I think you need explain more clearly the "pay" logic which (to me) is not obvious from your workbook.

So The workbook has two sections. The section to the left counts the days that the person took actual leave which counts ALL DAYS within the year (all 365 days). The Left section which contains the exact same data is for pay which means the days counted for pay.

Originally Posted by FlameRetired

I think the only way I am going to understand what you want is if you re-upload your workbook with all 6 of the expected values filled in manually.

As requested, please see attached

Originally Posted by JohnTopley

It appears your calculation is not based on working days but simply number of days between the two dates which I would have thought was incorrect.

If the normal working week is Monday to Friday then surely holidays deducted are only 5 days in any weekly period e.g. start date is 20/02/2017 (Monday) and end date is 26/02/2017 (Sunday) then holiday is 5 days which is what NETWORKDAYS.INTL will return with parameter for weekend being 1 (Saturday/Sunday).

Hence the result of 15 in your posted file.

If you want 7 days, then I will not accept the job!!!

Your right. Per the above worksheet i previously uploaded, The calculation is based on the total number of days between the two dates.

For the other column, the calculation is the same except the last working day that falls under a weekend (Saturday or Sunday) will not count so that would result in that day not being counted in the calculation.

So Monday through Sunday, Only Sunday should not count and Saturday will count unless its monday all the way through monday again then all dates will count. Makes sense?

7 days lmao... i don't know what you mean here!! :P

Originally Posted by FlameRetired

@ randy2012

I remain clueless.

Still subscribed.

Will watch.

I'm basically looking for a excel to automatically calculate work days. For the right and left columns in the worksheet, i would like it to calculate, up to 120 days, the amount that the worker takes paid vacation leave. The left side will be the amount of work leave the worker would be charged. In this case, maybe NETWORKDAYS.INTL might not be suitable... I don't know if there is any other function available or a way to modify NETWORKDAYS.INTL to count all the days in the middle of the calculation so that those days are counted and the worker is paid.

sorry for posting twice. let me know if i need to make a new thread with a new question. thanks.

sorry for posting again. let me know if i need to make a new thread with a new question. thanks.

I have added Sheet1 with some calculations but need rationale explaining as to my mind it is inconsistent when I try and work out the logic for "Days Counted".

I guess I cannot be helped. I will try to breakdown my explanation next time i post. Sorry for being vague and confusing.

Originally Posted by JohnTopley

Both have start dates of Monday, finish of Saturday: the second has a week's extension with finish on Saturday BUT has a day deducted from "Days Counted. ????

The third has Mon=>Sun AND has Day deducted from "Days Counted"

What are the dates in J & K?


NOTE: the dates in the example were formatted wirh "/" for my benefit

Correct. The second finishes on saturday because saturday is a weekend date and the company will not charge that employee that day because its a non-working day. Non-working days are not counted in the spreadsheet so that calculations aren't changed.

BUT if the employee decides to not call the company to let them know that he is returning on a non working day, that day he did not report in will be counted as a vacation day and he will have Sunday to report in or Sunday will be charged as well. Monday through Friday, regardless of employee status, will be charged by the back office, no exceptions. The only exceptions are weekends which are Saturday through Sunday which if he signed in on Sunday he will be charged as Monday Through Saturday.

J and K columns dates are days that the employee will be charged, no exceptions. Those dates will be sent to the back office for processing.

Note: If Column I has a date that is bigger then column H, Column I should be calculated instead of H because the employee is requesting more days off than his originally approved request which may be due to an emergency situation.

BUT if the employee decides to not call the company to let them know that he is returning on a non working day, that day he did not report in will be counted as a vacation day and he will have Sunday to report in or Sunday will be charged as well. Monday through Friday, regardless of employee status, will be charged by the back office, no exceptions. The only exceptions are weekends which are Saturday through Sunday which if he signed in on Sunday he will be charged as Monday Through Saturday.

BUT we have no way of knowing any of this from the information in the spreadsheet.

So like "FlameRetired" I remain clueless: I have no more suggestions than those already offered.

Originally Posted by JohnTopley

BUT we have no way of knowing any of this from the information in the spreadsheet.

So like "FlameRetired" I remain clueless: I have no more suggestions than those already offered.

Okay then just ignore the dates for J and K...

Im looking for just the calculation for the first column... I'll add a minus 1 or 2 for weekend dates on my own...


Thank you.