benhilt.blogg.se

It has been there since very early versions but tends not to be documented. If you want to know how many whole months or years lie between two dates you can either use the Month() calculation or a rather well hidden DATEDIF function which is a hangover from the migration from Lotus 123. Calculating how many months or years lie between two dates Number of working days between two dates. This calculation assumes the "start" is at the beginning of the day and the "end" is at the end of the day so is A1 = A2 the formula will return 1 working day. If you have the Start in A1, the end date in A2 and some holidays in the range B1:b5. Working days between two datesĮxcel has a great formula NETWORKDAYS which calculates the week days between a start date and end date and also removes defined holidays. You can also just add a number into the calculation in place of "+a2". Obviously if you want to add years you can move the +a2 up into the year section of the calculation. If you have your date in A1 and the number of months to be added in A2: To add whole moths (or years) to a date use the DATE formula which builds a date up from the year, month and day. You can't just add days to a date to make the 1 st of January become the 1 st of April. = EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0))+1,7) Adding months to a date Note that this also allows you to find the date of the end of the month in X months time by replacing 0 with X Finding the last Friday of the monthĪgain with a date in A1 the following calculation returns the last Friday in the month. If you have a date in A1 then the end of the month is

Working with dates in Excel is fairly easy however here are some tricks that I have found useful: Finding the end of a month