"I need to calculate each day how many days have elapsed since 1 January this year and the remaining days to conclude the year is there any function to perform these calculations? "
There is no direct role to perform these calculations, but the solution is simple using the DATE function and YEAR. Suppose you want to have the following information updated daily:
1. B2 formula is simple:
= TODAY ()
2. The formula for B3 is equally simple by using the WEEKNUM This function has only two arguments WEEKNUM ( serial_number ; Type ). The first argument refers to the date you want to know the number of weeks of the year where it belongs. The second argument is used to determine the type of week, that is, whether the week starts on Sunday, in which case the value write 1, or if we consider weeks whose first day is on Monday, in which case the value of this argument is 2. So, the resulting formula is:
WEEKNUM = (B2, 2)
3. To calculate the days since January 1 this year to date, use the following formula:
= B2-DATE (YEAR (B2), 1; 1) If we add descriptive text to the result we can use the & operator (CONCATENATE):
= B2-DATE (YEAR (B2), 1, 1) & "days from the beginning of the year"
4. Finally, to know how many days remain before the end of the year:
= DATE (YEAR (B2), 12, 31)-B2 adding descriptive text would be:
= DATE (YEAR (B2), 12, 31)-B2 & " days to finish the year "
If we keep this information always visible on the sheet we can use the option Freeze Panes. To do this we look at the A7 cell and go to menu Window / Freeze Panes . You receive a top edge across row 7 being immobilized cells that are above this line.
0 comments:
Post a Comment