ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Date Format Problems?? (https://www.excelbanter.com/excel-discussion-misc-queries/55266-date-format-problems.html)

nastech

Date Format Problems??
 
Date Format Problems??

Hi, I am a bit novice at some of this; any suggestions will help, (can
find/ using: format cells, number/custom.. cond. format..)

Date example that I was/ am using: Text: 051113, and "Right Justify" to
see just last 2 digits: 13 (for day)
I changed system date format in Control Panel, Regions, to be: yy-mm-dd
(did not really want dashes, trying for database type sorting of dates..?)
Don't know if I should pick a different format to do same thing.. to see:
yymmdd.

- Is there a work-around for seeing "text"? / last two characters of DATE?
When I format as a date, & narrow the column, & Right Justify (for space &
to see just the last 2 numbers), i get: ##
Trying to get ## to go away, and still be a date.


2nd problem: need Function for date older than 1 "Business" day. (for
Cond. Formatting), (note: for weekends-sat / sun, Friday is still today :)
is there a Date type Function to use?, Thanks, need something like:

IF( last "Business" DATE is one day old),true,false)




Gary''s Student

Date Format Problems??
 
Looking at your first problem I assume that 051113 means:
day 13
month 11
year 2005
and that all you text/dates are 6 characters. In a separate cell enter:

=right(A1,2) if the text/date in in cell A1. You can justify it anyway you
like.
--
Gary's Student


"nastech" wrote:

Date Format Problems??

Hi, I am a bit novice at some of this; any suggestions will help, (can
find/ using: format cells, number/custom.. cond. format..)

Date example that I was/ am using: Text: 051113, and "Right Justify" to
see just last 2 digits: 13 (for day)
I changed system date format in Control Panel, Regions, to be: yy-mm-dd
(did not really want dashes, trying for database type sorting of dates..?)
Don't know if I should pick a different format to do same thing.. to see:
yymmdd.

- Is there a work-around for seeing "text"? / last two characters of DATE?
When I format as a date, & narrow the column, & Right Justify (for space &
to see just the last 2 numbers), i get: ##
Trying to get ## to go away, and still be a date.


2nd problem: need Function for date older than 1 "Business" day. (for
Cond. Formatting), (note: for weekends-sat / sun, Friday is still today :)
is there a Date type Function to use?, Thanks, need something like:

IF( last "Business" DATE is one day old),true,false)




nastech

Date Format Problems??
 
Thankyou.. will see if get some response on 2nd problem too, so won't click
yes.. but looks like that might do what I need for 1st, thanks

"Gary''s Student" wrote:

Looking at your first problem I assume that 051113 means:
day 13
month 11
year 2005
and that all you text/dates are 6 characters. In a separate cell enter:

=right(A1,2) if the text/date in in cell A1. You can justify it anyway you
like.
--
Gary's Student


"nastech" wrote:

Date Format Problems??

Hi, I am a bit novice at some of this; any suggestions will help, (can
find/ using: format cells, number/custom.. cond. format..)

Date example that I was/ am using: Text: 051113, and "Right Justify" to
see just last 2 digits: 13 (for day)
I changed system date format in Control Panel, Regions, to be: yy-mm-dd
(did not really want dashes, trying for database type sorting of dates..?)
Don't know if I should pick a different format to do same thing.. to see:
yymmdd.

- Is there a work-around for seeing "text"? / last two characters of DATE?
When I format as a date, & narrow the column, & Right Justify (for space &
to see just the last 2 numbers), i get: ##
Trying to get ## to go away, and still be a date.


2nd problem: need Function for date older than 1 "Business" day. (for
Cond. Formatting), (note: for weekends-sat / sun, Friday is still today :)
is there a Date type Function to use?, Thanks, need something like:

IF( last "Business" DATE is one day old),true,false)




nastech

Date Format Problems??
 
yes yymmdd, actually looks like not enough room to do, no space for more
columns, don't know if have suggestion for same cell.

"Gary''s Student" wrote:

Looking at your first problem I assume that 051113 means:
day 13
month 11
year 2005
and that all you text/dates are 6 characters. In a separate cell enter:

=right(A1,2) if the text/date in in cell A1. You can justify it anyway you
like.
--
Gary's Student


"nastech" wrote:

Date Format Problems??

Hi, I am a bit novice at some of this; any suggestions will help, (can
find/ using: format cells, number/custom.. cond. format..)

Date example that I was/ am using: Text: 051113, and "Right Justify" to
see just last 2 digits: 13 (for day)
I changed system date format in Control Panel, Regions, to be: yy-mm-dd
(did not really want dashes, trying for database type sorting of dates..?)
Don't know if I should pick a different format to do same thing.. to see:
yymmdd.

- Is there a work-around for seeing "text"? / last two characters of DATE?
When I format as a date, & narrow the column, & Right Justify (for space &
to see just the last 2 numbers), i get: ##
Trying to get ## to go away, and still be a date.


2nd problem: need Function for date older than 1 "Business" day. (for
Cond. Formatting), (note: for weekends-sat / sun, Friday is still today :)
is there a Date type Function to use?, Thanks, need something like:

IF( last "Business" DATE is one day old),true,false)




Gary''s Student

Date Format Problems??
 
I have only one other suggestion, but its kind of cheesy. If you have a
number in a cell, you can not format the individual digits with thier own
fonts. You can format the individual characters in text string separately.

You have to do this in the formula bar:

Select the cell with 051113 in it. In the formula bar, highlight 0511 and
pull-down:

Format Cells... Font and set the color to the same as the background
color. This will make the 0511 seem to disappear, leaving only the 13.
--
Gary''s Student


"nastech" wrote:

yes yymmdd, actually looks like not enough room to do, no space for more
columns, don't know if have suggestion for same cell.

"Gary''s Student" wrote:

Looking at your first problem I assume that 051113 means:
day 13
month 11
year 2005
and that all you text/dates are 6 characters. In a separate cell enter:

=right(A1,2) if the text/date in in cell A1. You can justify it anyway you
like.
--
Gary's Student


"nastech" wrote:

Date Format Problems??

Hi, I am a bit novice at some of this; any suggestions will help, (can
find/ using: format cells, number/custom.. cond. format..)

Date example that I was/ am using: Text: 051113, and "Right Justify" to
see just last 2 digits: 13 (for day)
I changed system date format in Control Panel, Regions, to be: yy-mm-dd
(did not really want dashes, trying for database type sorting of dates..?)
Don't know if I should pick a different format to do same thing.. to see:
yymmdd.

- Is there a work-around for seeing "text"? / last two characters of DATE?
When I format as a date, & narrow the column, & Right Justify (for space &
to see just the last 2 numbers), i get: ##
Trying to get ## to go away, and still be a date.


2nd problem: need Function for date older than 1 "Business" day. (for
Cond. Formatting), (note: for weekends-sat / sun, Friday is still today :)
is there a Date type Function to use?, Thanks, need something like:

IF( last "Business" DATE is one day old),true,false)




nastech

Date Format Problems??
 
Would try anything that works, thanks for the idea. Think when I compress
the size / width of column, that will preclude that working..

Trying to figure out the date thing still, got this far. May have to repost
it if no answer here. Guesse I should only post 1 question at a time.

How do you backward / forward find date serial for 1900 system, to figure
equations (maybe just plug into a cell, see what it says: struggling with
that too.

Equation Looking for: How do I determine if date in a cell is 1 day old.

Something like: ?
=IF(DATE(DAY(A1)+1<DATE(TODAY(A1)),true,false) guessing something like this



XXXXXXXXXX

"Gary''s Student" wrote:

I have only one other suggestion, but its kind of cheesy. If you have a
number in a cell, you can not format the individual digits with thier own
fonts. You can format the individual characters in text string separately.

You have to do this in the formula bar:

Select the cell with 051113 in it. In the formula bar, highlight 0511 and
pull-down:

Format Cells... Font and set the color to the same as the background
color. This will make the 0511 seem to disappear, leaving only the 13.
--
Gary''s Student


"nastech" wrote:

yes yymmdd, actually looks like not enough room to do, no space for more
columns, don't know if have suggestion for same cell.

"Gary''s Student" wrote:

Looking at your first problem I assume that 051113 means:
day 13
month 11
year 2005
and that all you text/dates are 6 characters. In a separate cell enter:

=right(A1,2) if the text/date in in cell A1. You can justify it anyway you
like.
--
Gary's Student


"nastech" wrote:

Date Format Problems??

Hi, I am a bit novice at some of this; any suggestions will help, (can
find/ using: format cells, number/custom.. cond. format..)

Date example that I was/ am using: Text: 051113, and "Right Justify" to
see just last 2 digits: 13 (for day)
I changed system date format in Control Panel, Regions, to be: yy-mm-dd
(did not really want dashes, trying for database type sorting of dates..?)
Don't know if I should pick a different format to do same thing.. to see:
yymmdd.

- Is there a work-around for seeing "text"? / last two characters of DATE?
When I format as a date, & narrow the column, & Right Justify (for space &
to see just the last 2 numbers), i get: ##
Trying to get ## to go away, and still be a date.


2nd problem: need Function for date older than 1 "Business" day. (for
Cond. Formatting), (note: for weekends-sat / sun, Friday is still today :)
is there a Date type Function to use?, Thanks, need something like:

IF( last "Business" DATE is one day old),true,false)





All times are GMT +1. The time now is 05:06 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com