Using "Start date" Field to pull day as a text number to sort by

Question

Trying to pull the day out of "Start date" does not seem to give me the correct number and I have read where I should add a +1 to the formula but this will not work when you come to the first day of the month or last day of the month so I am first using this formula for both Day code and dayintext so that I can compare day and monthday value.

=text([Start Date],"dd") give the following results

 title, start date, daycode, dayintext
 PMReports,   8/1/2012 9:00 AM, 1, 08/01
 PMReports,  10/1/2012 9:00 AM, 1, 10/01
 PMReports,   11/1/2012 9:00 AM,1, 11/01
 PAS Meeting,   7/16/2012 3:00, PM 16, 07/16
 PMReports,   1/2/2013 9:00 AM, 2, 01/02
 VRUpload,   9/26/2012 11:00 AM, 26, 09/26
 VRUpload,   11/26/2012 11:00 AM, 26, 11/26
 VRUpload,   7/27/2012 11:00 AM, 27, 07/27
 VRUpload,   12/27/2012 11:00 AM, 27, 12/27
 VRUpload,   8/29/2012 11:00 AM, 29, 08/29
 VRUpload,   10/29/2012 11:00 AM, 29, 10/29
 PMReports,   12/3/2012 9:00 AM, 3, 12/03
 PMReports,   9/4/2012 9:00 AM, 4, 09/04
GVReport   7/17/2012 12:00 AM, 16, 07/16
GVReport   7/17/2012 12:00 AM, 16, 07/16
SVReport   7/18/2012 12:00 AM, 17, 07/17
GuReport   7/19/2012 12:00 AM, 18, 07/18
GpReport   7/19/2012 12:00 AM, 18, 07/18
GZReport   7/23/2012 12:00 AM, 22, 07/22
DTReport   7/24/2012 12:00 AM, 23, 07/23
GHReport   7/28/2012 12:00 AM, 27, 07/27
KPReport   7/28/2012 12:00 AM, 27, 07/27
GAReport   7/4/2012 12:00 AM, 3, 07/03

Notice that when the default time is used 12:00 am the date is not correct

I am using Sharepoint 2010 sp1 with hotfix kb2536599, 14.0.6029.5000

Reason they need the day as a text is so that the views can be created to reflect when days are needed within a subgroup.  I am able to produce the subgroups with the current fields but now I need to figure out how to get the correct day in the field

Any help on this would be appreciated and hope its not a long formula for something I think should be simple.  I thought of changing the start date to 1:00am instead of 12:00 AM which may be why this is happening but I am just guessing from all the write-up I am reading.  Adding the +1 does not work since it wont calculate the first or last day correctly. 

---

WorkerBee09

Answer 1

Hi,

You can try th formular below:

=IF(TEXT([Start Date],"hh:mm")="00:00",TEXT([Start Date],"dd")+1,TEXT([Start Date],"dd"))

---

Xue-mei Chang

TechNet Community Support

Answer 2

Thanks but is also did not work for the items that were defaulting to 12:00am

It worked well for the ones where the date time was not 12:00am but for some reason it just seems there is something that is missing so I created this formula to show me the day and time mm/dd hh:mm on a second column and to my surprise it shows 20:00

title, Start Date, DayCode, DayinText

 name1,   7/22/2012 12:00 AM, 21,  07/21 20:00
 name2,   7/22/2012 12:00 AM, 21,  07/21 20:00
 Name3,   7/23/2012 12:00 AM, 22,  07/22 20:00
 Name4,   7/25/2012 12:00 AM, 24,  07/24 20:00
 Name5,   7/1/2012 12:00 AM,   30,  06/30 20:00
 Name6,   7/31/2012 12:00 AM, 30,  07/30 20:00

I am still looking to see why its showing 20:00 hrs since this is not what is showing in the calendar item.  Not sure why this is?

---

WorkerBee09

forgot to add the formula for other to see:

Placed yours in the daycode and the one below in the DateInText

=TEXT([Start Date],"mm/dd"&" "&"hh:mm")

Answer 3

So I changed your code to use the 20:00 instead of 00:00 and that fixed most of them except the first day of the month which showed for 7/1/2012, and the daycode was 31

Name5   7/1/2012 12:00 AM 31 06/30 20:00
Name6   7/31/2012 12:00 AM 31 07/30 20:00

so it looks better than before but I will always be doubting that it will reflect a wrong number and a penalty will be imposed for not having the correct date.  Imagine a payment has to be made at the beginning of a month and its missed by 30 days!! Not Good.  So I have to find out why its giving me the wrong time for the date even if it shows correctly as 12:00am.

Kind of weird for something so simple. But thanks for the formula.  I was thinking I was on the wrong track but will add logice to the madness and show an error if it does not meet the correct number somehow.

---

WorkerBee09