Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I convert a number(1-365) to a month and day of a year?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I convert a number(1-365) to a month and day of a year?
Right-click FormatCells NumberTab select a TYPE
Vaya con Dios, Chuck, CABGx3 "rnc" wrote in message ... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I convert a number(1-365) to a month and day of a year?
With
A1: (a number between 1 and 365) Try this: B1: =DATE(YEAR(TODAY()),1,A1) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "rnc" wrote in message ... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I convert a number(1-365) to a month and day of a year?
From Chip Pearson's Web Site (www.cpearson.com)
Converting A Julian Date To A Standard Date The formula below will convert a Julian date to a standard Excel date. =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3)) If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the date is assumed to be a 2000 century year. If the year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to be a 1900 century year. This formula works by taking advantage of the fact that the DATE function can handle days beyond the "normal" days in a month. For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999. These Julian dates must have the leading zero or zeros for years between 2000 and 2009. For example the 123rd day of 2000 must be entered as 00123. Format the cell as TEXT before entering the data, or enter an apostrophe before the Julian date -- e.g., '00123. This will prevent Excel from treating the Julian date as a number and suppressing the leading zeros. Tyro rnc" wrote in message ... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I convert a number(1-365) to a month and day of a year?
Hi,
If your number is in cell A1, enter the formula: =EDATE(A1,1296) Assuming you mean for the current year? In another cell. If you are using Excel 2003 or earlier you will need to attach the add-in Analysis ToolPak. In 2007 that won't be necessary. The format the result to show month and day. Or if you don't want to use the Analysis ToolPak use the formula: =A1+39447 These work for the current year. If you want it to work for any year you can modify the above formulas or use: =DATE(YEAR(NOW()),MONTH(NOW()),0)+A1 the first formula can be generalized to: =EDATE(A1,12*(RIGHT(YEAR(NOW()),2)+100)) Hope some of these help. Cheers, Shane "rnc" wrote in message ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert X-Axis Scale from Day/Month/Year Options to Actual Numbers | Charts and Charting in Excel | |||
How do I convert a month-date format to day number of the year? | Excel Worksheet Functions | |||
convert number to month and year in formula | Excel Worksheet Functions | |||
Display number of day of month depending on the year | Excel Discussion (Misc queries) | |||
How do I convert a serial number to the month, day, and year in E. | Excel Discussion (Misc queries) |