Date calculation question

[jared511]

Hello,

I have a date field in one of my cells that shows March 3, 2011.


I was wondering how I can get one sell to reflect the number 2 since 2 days have passed in march. And another cell to reflect the number 29, since their are 29 days left in March. Any help would be greatly appreciated.

 

[barry houdini]

If your date is in B2 try

=DAY(B2)-1

and

=EOMONTH(B2,0)-B2+1

 

[jared511]

Ok so here is the problem.

The date field shows "March 03, 2011 MTD". This formula is in that cell
=CONCATENATE(TEXT(Data!C2,"mmmm dd, yyyy")," MTD")

Is there anyway I can create a formula to back out the MTD part? I tried doing a left formula backing out the characters besides MTD, but that didn't help.


Thanks,
Jared

 

[barry houdini]

So your original date is in Data!C2....in which case it's easier to just reference that date directly, i.e.

=DAY(Data!C2)-1

and

=EOMONTH(Data!C2,0)-Data!C2+1

Format the cells with those formulas as "general"

 

[jared511]

of course lol. Just wasn't thinking. Thanks!