PDA

View Full Version : [SOLVED] Getting Month and Year from a date cell



marshybid
05-09-2008, 08:57 AM
Hi There,

Can anyone help with the following please.

I have 2 columns containing dates - Start Date and End Date - I need to add a further 3 columns - Start Month, End Month and Hours

I can not work out the correct formula to use to extract the month and year info from the original cell and format it as mmm - yyyy

I also need to calculate the nu7mber of weeks between start and end date * 37.5 to give me hours.

I have attached an example sheet for reference.

Thanks in advance. : pray2:

Marshybid

Bob Phillips
05-09-2008, 09:12 AM
You could just put =A" and format as mmmm yyyy, or else use

=TEXT(A2,"mmmm yyyy")
=TEXT(B2,"mmmm yyyy")

Do you want whole weeks? If so, rounded up or down, when does a week start? If not

=(B2-A2)/7*37.5

marshybid
05-09-2008, 09:19 AM
Thanks xld. I can't believe I hadn't even considered using the Text formula :banghead: I was trying Month/Year etc formulas. DOH!!!!



Do you want whole weeks? If so, rounded up or down, when does a week start? If not

=(B2-A2)/7*37.5


I would ideally like to calculate total accurate number of working days (5 days per week) between start and end date * 7.5 per day to give me my hours (I don't know if NETWORKDAYS would do it)??

Marshybid

Bob Phillips
05-09-2008, 10:08 AM
That would just be


=NETWORKDAYS(A2,B2)*7.5