Equivalent of DatePart in Excel

[EliseFreedman]

I have a spreadsheet in Excel which gets updated weekly. I have a cell which contains the date that the report relates too. I have another cell further down which calculates the % of the month which has passed

e.g todays date = 14th May
55% of May(=(31-14)/31*100).

Is it possible to extract the day of the month(i.e 14) from the date and then use it in the formula. I know about using datepart in Access. Not sure if there is an equivalent in Excel

 

[bluedragon2]

=DAY(<date reference>)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

That will work if the cell is formated as date.

If it is text, then give an example and see what we can do.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Dreamboat]

Not sure how you would work it out finally, but from here:

http://www.cpearson.com/excel/DateTimeWS.htm

If he doesn't have a direct function for it, you could probably take today minus the first day of the month to get the Nth day of the month.

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[xlbo]

I'm not sure ther eis an equivalent for getting the day of the month BUT, you can use

=Value(Left(text(A1,"dd/mm/yyyy"),2))

where your date is in A1

You can also use
=VALUE(LEFT(TEXT(EOMONTH(A1,0),"dd/mm/yyyy"),2))

To get the number of days in the month - this requires the Analysis Toolpak (Tools>Addins>Analysis Toolpak) to be installed


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question