Conditional Formatting for Weather Precipitation, Temperature and Dates

Question

First time posting so a picture of the excel sheet is attached to the hyperlink.

I would like to format my weather data as seen in column P. The column would go, first cell (# of days in month”,”Three letter Abbreviation of Month”-“last two digits of year). Then combine column M and column E in that order with a comma delineation below it for each day in the month. After each month is has been completed, I would like to insert a row with the next months information as stated above (# of days in month”,”Three letter Abbreviation of Month”-“last two digits of year).

There is roughly 10,000 rows of data. I believe I will have to use a Macro or Conditional Formatting but do not know how to write this out. The row insert is not important as long as I can drag the formula down the column and still get the right corresponding Precipitation and Temperature Average numbers with the dates.

Answer 1

In cell P1, enter the formula:

=IF(ISNUMBER($C1),$M1&","&$E1,TEXT(DATE(YEAR($C2),MONTH($C2)+1,0),"d,mmm-yy"))

Then copy cell P1 and paste it to all the other cells in column P.

The way it works is that the IF function checks whether column C contains a number (such as a date). If so, it returns the values from columns M and E with a comma between them ($M1&","&$E1). Otherwise, this must be the gap between months and you want the number of days in the month as well as the data in mmm-yy format.

The the date of the first of the month will be one row down in columns C. The formula DATE(YEAR($C2),MONTH($C2)+1,0) returns the date of the last day of that month (actually, the zeroth day of the next month, but it works to get the last day of this month), and the TEXT function formats that date with the day first followed by a comma and the mmm-yy part of the date.