Date formatting

[robw]

Hi

I have a list of dates as follows...
Fri 01-Jun-18
Fri 29-Jun-18
Wed 01-Aug-18
Fri 31-Aug-18
Mon 01-Oct-18
Thu 01-Nov-18

These are actual paydays associated with the nominal payday of "first the month". ( If the 1st falls on a weekend you get paid on the previous Friday)

Is there a way of Formatting these so that the actual dates are retained unchanged in the respective cell values, but actually displaying them as "Jun", "Jul", "Aug", "Sep" etc. Obviously I can do this by introducing another column.... but is there a way I can I do it "IN PLACE" by clever date formatting?

Many Thanks
Rob

 

[Joe4]

Is there a way of Formatting these so that the actual dates are retained unchanged in the respective cell values, but actually displaying them as "Jun", "Jul", "Aug", "Sep" etc. Obviously I can do this by introducing another column.... but is there a way I can I do it "IN PLACE" by clever date formatting?

As long as those are valid date entries (and not text - you cannot format text), just change the format of the cells to "mmm".

 

[fhqwgads]

if your cell contains a valid date value you can customise the format to mmm but if you just have text made to look like a date it will be more complicated

 

[robw]

Thanks Joe. Yes, these are valid date entries ( currently custom formatted as "ddd dd-mmm-yy"), and I do understand the concept of date formatting strings. My problem is a little deeper than I think you have noticed; for example, formatting with "mmm" would display the cell containing Fri 29-Jun-18 as "Jun", whereas this is the July payday, so I want to see "Jul" . Do you see my problem?
What I am really trying to achieve is to use this list of dates as the X-values in an X-Y chart, where the X position of a point is determined by the cell value (29 Jun in the above example), but the label attached to that point is displayed as the nominal pay-month string ("Jul" in the above example). Any thoughts welcome.

 

[Joe4]

My problem is a little deeper than I think you have noticed; for example, formatting with "mmm" would display the cell containing Fri 29-Jun-18 as "Jun", whereas this is the July payday, so I want to see "Jul" . Do you see my problem?

I don't think you are going to be able to handle that with "straight up" formatting. Formatting simply formats the current data in the cell, it does not do mathematical computations on the cell, and then format that.

I think you might need a helper column. Since a weekend is at most two days, you could simply use a formula in this helper column that adds two to your date and format that cell as "mmm".

 

[fhqwgads]

how are you getting your dates? if they're references to other cells or data you could use a formula

Book1
	O	P
54	24/02/2018	Feb
55	25/02/2018	Feb
56	26/02/2018	Mar
57	27/02/2018	Mar
58	28/02/2018	Mar
59	1/03/2018	Mar
60	25/03/2018	Mar
61	26/03/2018	Apr
62	27/03/2018	Apr
63	28/03/2018	Apr
64	29/03/2018	Apr
65	30/03/2018	Apr
66	31/03/2018	Apr
67	1/04/2018	Apr
Sheet1

Cell Formulas
Range	Formula
P54	=TEXT(MONTH(EOMONTH(O54,(DAY(O54)>25)+0))*29,"mmm")

if the dates are entered into those cells manually then i dont see why you cant just enter them is as what you want

 

[Joe4]

fhgwgads,

I think that may be overcomplicating it a bit. This is what they said.

( If the 1st falls on a weekend you get paid on the previous Friday)

So, it seems that it could only be three possible days, the 1st of a month, or one of the last two days of a month.
In which case, simply adding 2 days to the date will be guaranteed to get you in the right month.

So, you could simplify your formula to this:

=TEXT(054+2,"mmm")

(or just add 2 and use the date format of "mmm", like I suggested)

If things like holidays need to be accounted for, just change the "+2" part to something like "+5". Based on the assumptions, you will still fall in the right month.