Seniority Dates - Vacation Reset Year
Jul 25, 2012
I need to take the month and day of a seniority date and add it to the reset year but am having difficulty doing so. If the seniority date if July 4, 2008, I need to convert it to July 4, 2012 for my calculation to work. My formula is this:
=IF(TODAY()>(E3+365),(H3-(SUMIF($J$2:$BK$2,"Taken",J3:BK3))),"")
Which works fine for all seniority dates in 2011. These are my column headings:
E
F
G
H
I
J
L
PPE
2011
5/5/2012
5/12/2012
Hire Date
# of Days
Balance
Cur Yr Accr
Accr
Accrued
Taken
Accrued
Taken
View 1 Replies
ADVERTISEMENT
Nov 6, 2009
SOLVED: adjusted functions. creating a vba macro for other reset goals. other goals have changed as well.
I have a table where the subsheets have the specific days that an employee uses vacation, sick, personal or other hours of off time. i wrote alot here to get my goals/thoughts down
my goal is to master one section then apply it to the other vacation date types. i'm stuck on going with a formula or using vb. also another concern is odd months such as someone hired 10/31 compared to a month like 6/30. not sure if this is a real concern.
a. need to reset vacation hours for the year based on annual anniversary date
-"vacation hours per year"
-"vacation hours remaining" =E8-('Vacation 1'!B8+'Vacation 2'!B8)
this data is taken from the subsheets. there is vacation 1 for jan-jun and vacation 2 for jul-dec
1year=40 hours
after 1 year anniversary =80 hours
3 years+ =120 hours
Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)
second part of my project:
b. vacation hours remaining can be carried over up until 3 months after their anniversary date. at this point they are wiped back to 0 until the next anniversary date (where the carry over hours recalculate)
ATTACHED TEMPLATE
things i am changing in design:
vacation hours per year (to include a formula to calculate anniversary date, with the amount given for 0 year, 1 year, 3 years....) rather than a manual added # (which was lets say 40 hours+ carry over hours)
-thats why i am separating the carry over hours... which i'm hoping to auto calculate by taking
anniversary date - 3 months in a statement to reset it back to 0 after the three month period.
View 7 Replies
View Related
Mar 5, 2012
I have a vacation data of my company. I want to calculate difference between two dates (relieving date & rejoining date) after excluding holiday(which I will decide as per my company policy) also note that now i m in gulf country hence here Friday is weekly off not Sunday.
View 9 Replies
View Related
Nov 11, 2009
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)
basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 1-2 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure. http://www.ozgrid.com/News/excel-eva....htm#ExcelTips
View 3 Replies
View Related
Jul 10, 2009
I want to create a function that will check 2 user input dates (and anything in between) to see if it lies in a defined tax year. I will define the tax year in Cells A1: 6/4/2009 and A2: 5/4/2010 and ask the user to input dates in Cells B1 and B2 to check if any of these dates (or anything in between) falls between A1 and A2.
For example: Tax Year is 6th April 2009 - 5th April 2010, Cell A1 is 6/4/2009 and A2 is 5/4/2010. User inputs 2nd March 2009 in Cell B1 and 10th May 2009 in B2. Because the 10th May falls in the tax year the output should be "True". I can Excel to check one user defined number (Cell B1) using this =IF(AND (B1>=A1,B1<=A2),"Yes","No") but not sure which route to take to check 2 numbers B1 & B2 and anything in between.
View 2 Replies
View Related
Jul 13, 2014
i am using excel to create 8 groups that does night shifts for 4 days then another group does it afterwards and so on. instead of putting the dates manually and adding 4 days after the start of each night shift, is there a way or a formula i could use to just make it easier so that i wont be able to just manually do the dates each year? i've put a screenshot copy of the excel file that i am working on.
View 1 Replies
View Related
Feb 9, 2009
I imported a large amount of data from another program. The data is in one column and is in date format (02/24/57), for example. The data is made up of my clients birthdays. I want to import those birthdays into my outlook to create reminders but the dates are years past. I need to change the birth year to the current year in order for the reminder to activate going forward. If I dont update the year, the dates import back to the date they were born. In some cases 60 years back.
I am hoping that I dont have to manually change each date before I attempt to import into outlook.
View 5 Replies
View Related
Apr 30, 2013
I want a list of working dates for the year in excel. Wanted to have it so that there was some flexibility in the table too, but cannot get it to work 100%.
For some reason in the below example, in December, 26th shows up (despite being in the list of holidays) and the 29th (Sunday) shows up too.
[URL] .....
View 9 Replies
View Related
Mar 22, 2007
I'm trying to put in dates into my spreadsheet depending on the year I enter in. in cell A1, I put in the year (2007). In cell S2, I want to have the first day of the first month to show up, so my formula is =DATE($A$1,COLUMN()-18,1). That works great, it shows up as 1/1/07, which I want. Now my question is, is there a way to copy the formula over to the right, leaving three blank cells and having 2/1/07 show up in cell W2, 3/1/07 in cell AA2, and so on. Right now when I copy it over, what shows up is: 5/1/07 in cell W2, 9/1/07 in cell AA2, and so on.
View 9 Replies
View Related
Jan 24, 2008
I trying to find a formula to give the difference between two dates in year and month.
For example, the start date is Feb 1 2006 and end date is Jan 1 2008.
The formula should result in 2.11
I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11.
[url]
View 9 Replies
View Related
Oct 1, 2009
I have Month/Year dates in Column A. Eg. A7= Aug-2009.
I need this to be separated as follows:
Cell C7 must only show the month name in full eg. August
Cell E7 must show the year only eg 2009.
View 3 Replies
View Related
Aug 7, 2007
How can I lookup the value depend on Which year in the period?. I have prepared clean data attached. I use date calculation to find how many days in the period and then divide by 365 day to count the year. But it doesn't work.
View 5 Replies
View Related
Aug 20, 2007
I am trying to calculate the number of days it takes to complete a project when the project ends in a specific month. If it is July 2007 I want the number of days for each project completed in July 2007. The number of days is not the problem it is reflecting the number when a project takes longer than one month to complete. To add to things the users sometimes have "open" or "00/00/00" in E (for never close).
I have a spreadsheet with dates a project begins in column D and dates the project ends in column E and a start and end date for each month going across rows 1 and 2. When the project starts and ends in the same month life is good and my original formal of greater than and less than the dates (courtesy of this website) is effective. I tried the following formula in F34. Each piece of the formula worked but when I combined it I either broke it or have too many nested formulas.
=IF(OR($E34="OPEN",$E34="00/00/00",$E34>F$2),"",IF(AND(MONTH($D34)=MONTH($E34),IF($D34>F$1,$E34<F$2)),$A34,IF(AND(MONTH($D34)>MONTH($E34),if(MONTH($E34)=(MONTH(F$2)-1)),$A34,""))))
I created a work around by putting formulas in B and C and row 3 that would answer some of the nested if and working off the results. I was hoping someone might be able to help me make this a little more effecient.
View 9 Replies
View Related
Mar 13, 2008
If I have a date which is 03/09/2006. How can I create a formulae to abbreviate this. Ie to return 09/2006 (I am english so we have the DD and MM the other way round to you guys in the states).
In other words if an event happens on the third of september 2006 I want a column which classifies that event as september 2006 with NO reference to the day.
View 3 Replies
View Related
Apr 14, 2014
Attached spreadsheet has the resource names and their date of joinings (Column - F2), now I would like group the date of joinings into Month & Year format. I tried text formula and then converted it back to date format but its giving incorrect results. For ex - Column F2 has 9th Sep -2013, whereas my formula in H2 is showing Sep-2014. Any way to group these dates into months in a simpler way?
Once I have this grouping done, I would like to do a pivot and sort them.
View 5 Replies
View Related
Nov 12, 2008
My intentions are for Excel to recognize a series of dates as a particular week in the year. For example: 12/28/2008 thru 1/3/2009 equals Week 1, 1/4/2009 thru 1/10/09 equals Week 2.
I cannot for the life of me calculate a working formula.
View 7 Replies
View Related
May 8, 2012
i'd like a formula to change a date to month and year
Column E
11 May 201123 November 201108 July 201103 September 201111 March 201224 December 2011
I've used =Text(e1,"MMM") to pull the month through but would like to include the year too.
View 3 Replies
View Related
Jan 22, 2008
I want to be able to count the number of days in a specific year between two dates.
Suggested formula input: DaysInYear(Date1,Date2,Year)
Examples:
DaysInYear(3/3/2005,3/3/2006,2006) should return 62 (31 in Jan, 28 in Feb and 3 in Mar.)
DaysInYear(3/3/2005,3/3/2007,2006) should return 365
View 4 Replies
View Related
Jun 18, 2008
I have the below find and replace code that is now working. I double checked the syntax and can't find a reason why. The errors I get are Overflow and 400.
'format column with custom date format.
Public Sub formatDate()
Dim charHold As Date
What = m / d / yyyy
repl = yyyymd
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 3 Replies
View Related
May 11, 2013
I am trying to count dates from a list using sum product (I found the formula via google) I have plugged it into my spreadsheet but it does not seem to be calculating correctly.
I have attached the spreadsheet too : sumproduct_Error.xlsx
=SUMPRODUCT(YEAR(Tbl_finish[Finish]=2008)*(MONTH(Tbl_finish[Finish]=1)))
I am expecting a count of all the dates in January 2008 with the formula above.
View 5 Replies
View Related
May 19, 2014
I am trying to calculate the number of months in a specific year between two dates.
For example.
Start date 01/06/2012
End Date 01/02/2013
Number of months in 2012 = 6
Number of months in 2013 = 2
How can I write a formula to give me the answer of 6 & 2 from the start and finish dates?
View 10 Replies
View Related
Jul 11, 2014
I have a hotel room schedule of up to 1,000 rows. Each row is a different room, each column representing a different day of the year. Each cell in a row either has the surname of a guest for that particular day, or the word "Available" to indicate no guest is occupying that room on that day.
analysing each room's occupancy and availability periods over the year. i.e.
- what day(s) a guest occupied and vacated a room,
- what day(s) a another guest then occupied and vacated that room, or
- what day(s) it was then "Available" from
The dates of occupancy are needed in case the guest queries the period.
Parameters
- All 365 days of the year are used - irrespective of holidays/ weekends - the room will either be occupied or available - The day after a guest leaves, it will automatically become available until unless another guest occupies it - The same guest may use a room more than once in a year. This will need to be shown as a unique occurrence, not accumulative
Design
- A1:A1000 is a list of room numbers
- B1:NB1 are all 365 days of the year regardless of weekends/ holidays
- Every cell has either the surname of the guest or the word "Available"
- The results of the analysis can be on another worksheet
Sample I have attached a scaled down sample of what I'm after. Row 1 has the dates starting from 30 April 2013 to 23 May 2013. Room numbers in Column A. C10:D26 are samples for the analysis I'm after. I've randomly chosen Room 100 and Room 500. Room 500 has Mr Mathews staying 4 times.
View 5 Replies
View Related
Jul 5, 2009
I would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays.
So A1 would be 1/01/09
Then Excel would list
Thursday, Jan 01
Friday, Jan 02
Saturday, Jan 03
Monday, Jan 05
Tuesday, Jan 06
etc....
I've tried messing with the WEEKDAY function, but can't seem to figure it out.
View 9 Replies
View Related
Nov 7, 2009
I am needing to create a formula that will calculate the number of paychecks and the dates for the calendar year. There is some employees that are paid bi-weekly and some bi-monthly.
For Example:
A1-Employee, A2-Hire Date, A3-Pay Frequency, A4-Number of Paychecks
Based on the hire date and pay frequency it will calculate the number of paychecks in A4.
Is this something that can be calculate with excel or would it need to be done manually.
View 9 Replies
View Related
Nov 16, 2009
I have a data chart that lists dates in various types of formats, for example:
1/1/10
1/1/09
Jan
Jan '10
Jan/Feb '10
Jan/Feb
I'm trying to figure out a formula that will just pull the month and year no matter what format the dates are in. Based on the above I'm looking for the following results:
1/1/10: Jan 10
1/1/09: Jan 09
Jan: Jan 09
Jan '10: Jan 10
Jan/Feb '10: Jan 10
Jan/Feb: Jan 09
This data is for the entire year so the same applies for all months.
View 9 Replies
View Related
Aug 3, 2014
I would like to automate a sheet to report statistics of projects based on a single cell that is the year that a user manually inserts. I've got a separate worksheet with all dates manually set up for next 10 years and would like it to be read by formulae in a reporting worksheet based on the year that user enters.
So if I specify year 2014/15 (financial year), I would like the cell C1 under "Reporting" sheet to display 1/07/14 predetermined in "Dates" B3, if I specify 2015/16, the cell C1 will read 1/07/15 from "Dates" B6 and so on.
I could do it with "IF" function but there will be too many nested IF functions in a lot of cells (hundreds if not thousands). This may make my file size very large.
Attached is a simplified example of what I mean.
View 7 Replies
View Related
Dec 27, 2013
I want to sum values in a column only if the year of the date in the adjacent column is <= to the year in cell F61. the column to evaluate is in the format d/mm/yyyy, and F61 is just yyyy. What I want to accomplish but it doesn't work:
=SUMIF(year(I9:I57),"<="&F61,H9:H57)
View 1 Replies
View Related
Jan 20, 2010
I want to use a COUNTIF to return the sum of all the dates that fall within a given month/year. For example: E1 Contains the date July-2009
Column A has date entries such as July 3, 2009, July 18, 2009, August 4 2009. In F1 I want to return the sum of all dates that fall within the month of E1.
View 4 Replies
View Related
Jan 16, 2014
I am looking for Macro code preferably to get list of dates with Saturday / Sunday in a separate columns which falls Saturday and Sunday on imputing the year.
View 5 Replies
View Related
May 30, 2007
I want to create a monthly timesheet which contains 9 columns for
(Date, Day, Project no., Activity, Time In, Time Out, Total Hours, OT Hours, Remarks)
I have used IF Function to calculate Total Hours & OT hours automatically. Time IN & Time OUT, Project No., will be entered manually on daily basis.
Weekday function is used in the Day column to return the corresponding day of the date in the Date column.
Name of the Month and Year will be manually entered in the designated cells I3 and I4 respectively.
Now the solution I am looking for is, the dates should be automatically entered in the Date column (in cells A8 to A38) based on the Month & Year entered in cells I3 & I4. Dates of the corresponding month of the year should only be filled in. (If a month is not having 29, 30 or 31st day, the corresponding cells should be left blank. i.e. nothing should be displayed in the corresponding cells). I am looking for some sort of formula to enter in the cells of Date column (A8 to A38) achieve this. I have searched the forum and could not find anything which could at least give me an idea about the kind of function or formula to be used.
Attached here is the time sheet I am trying to create.
View 8 Replies
View Related
