Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
texcel
 
Posts: n/a
Default Combine & Display “Fixed” & “Automatically Updated” Date Parts


Hello Excel Forum! Good day to you all. Question I been grappling with:


ISSUE TO BE SOLVED:

My Work Season starts every year on a FIXED MONTH and DAY (1/1 or
January 1) EVERY YEAR…

** DESI

I want a Formula that will DISPLAY the FIXED DAY and MONTH
(NUMERICALLY) that my Work Season starts—1/1—ALONG WITH the “CURRENT
YEAR” and THE “DAY OF THE WEEK” that the FIXED DAY and MONTH fall on
(e.g Fri, Sat, Sun, etc) DURING THE CURRENT YEAR.

With the previously described desire the cell would
display–THU/1/1/2004–for the remainder of this year and next year it
would display–SAT/1/1/2005

PURPOSE:

I want this so that the exact Start Dates of my Work Season can be SEEN
and USED AS REFERENCES IN MORE EXCEL CALCULATIONS such as adding or
subtracting dates


** DESI IN OTHER WORDS:

I want a cell that will DISPLAY all of the below-listed “PARTS OF DATE”
TOGETHER WITHIN IT in a manner that will allow me to perform Date
Calculations on it.


1. A “FIXED NUMERICAL MONTH AND DAY” EVERY YEAR THAT WILL NOT CHANGE:

1/1


2. AFTER THE “FIXED NUMERICAL MONTH and DAY” I want THE “CURRENT YEAR”
to display and to AUTOMATICALLY UPDATE EVERY YEAR:

For the remainder of this year (2004) it would AUTOMATICALLY read:

1/1/2004

And next year (2005) it would AUTOMATICALLY read:

1/1/2005

And in 2006 it would AUTOMATICALLY read:

1/1/2006


3. AND BEFORE THE DATE (1/1/2004, 1/1/2005, etc) I want THE “DAY OF THE
WEEK” that the “Fixed Numerical Month and Day” falls upon DURING THE
“CURRENT YEAR”


** SO THAT, ALTOGETHER THE CELL WOULD DISPLAY:

For the remainder of this year (2004) the cell would display:

THU/1/1/2004

And next year (2005) the cell would display:

SAT/1/1/2005

And in 2006 it would display

SUN/1/1/2006


PLEASE NOTE:

The FIXED NUMERICAL MONTH and DAY remain constant and…

Once I enter the FIXED NUMERICAL MONTH and DAY along with whatever
Formula…

I want the “CURRENT YEAR” and the “DAY OF THE WEEK” that the “FIXED
MONTH and DAY” fall upon during the “CURRENT YEAR” to ALL UPDATE
AUTOMATICALLY EVERY YEAR

THANKS FOR YOUR CONSIDERATION AND PLEASE RESPOND IF YOU CAN,

Thanks

Texcel

P.S. If you can do the above described…

Is there any way to add a “FIXED TIME” to the Display e.g.
SAT/1/1/2005/6:00pm, SUN/1/1/2006/12:00AM


--
texcel
------------------------------------------------------------------------
texcel's Profile: http://www.excelforum.com/member.php...fo&userid=4325
View this thread: http://www.excelforum.com/showthread...hreadid=274211

  #2   Report Post  
Ray S
 
Posts: n/a
Default

Use the formula;
=Date(year(now()),1,1)

Format the cell using custom and enter
Ddd/m/d/yyyy

This will allow you to use the cell as a date number as well. If you want
it as text you would use the same formula above but include it in the text
function as follows:
=text(date(year(now()),1,1),"Ddd/m/d/yyyy")

That should do it. The only thing is, is you'll need that on each workbook.

"texcel" wrote:


Hello Excel Forum! Good day to you all. Question I been grappling with:


ISSUE TO BE SOLVED:

My Work Season starts every year on a FIXED MONTH and DAY (1/1 or
January 1) EVERY YEAR€¦

** DESI

I want a Formula that will DISPLAY the FIXED DAY and MONTH
(NUMERICALLY) that my Work Season starts€”1/1€”ALONG WITH the €śCURRENT
YEAR€ť and THE €śDAY OF THE WEEK€ť that the FIXED DAY and MONTH fall on
(e.g Fri, Sat, Sun, etc) DURING THE CURRENT YEAR.

With the previously described desire the cell would
display€“THU/1/1/2004€“for the remainder of this year and next year it
would display€“SAT/1/1/2005

PURPOSE:

I want this so that the exact Start Dates of my Work Season can be SEEN
and USED AS REFERENCES IN MORE EXCEL CALCULATIONS such as adding or
subtracting dates


** DESI IN OTHER WORDS:

I want a cell that will DISPLAY all of the below-listed €śPARTS OF DATE€ť
TOGETHER WITHIN IT in a manner that will allow me to perform Date
Calculations on it.


1. A €śFIXED NUMERICAL MONTH AND DAY€ť EVERY YEAR THAT WILL NOT CHANGE:

1/1


2. AFTER THE €śFIXED NUMERICAL MONTH and DAY€ť I want THE €śCURRENT YEAR€ť
to display and to AUTOMATICALLY UPDATE EVERY YEAR:

For the remainder of this year (2004) it would AUTOMATICALLY read:

1/1/2004

And next year (2005) it would AUTOMATICALLY read:

1/1/2005

And in 2006 it would AUTOMATICALLY read:

1/1/2006


3. AND BEFORE THE DATE (1/1/2004, 1/1/2005, etc) I want THE €śDAY OF THE
WEEK€ť that the €śFixed Numerical Month and Day€ť falls upon DURING THE
€śCURRENT YEAR€ť


** SO THAT, ALTOGETHER THE CELL WOULD DISPLAY:

For the remainder of this year (2004) the cell would display:

THU/1/1/2004

And next year (2005) the cell would display:

SAT/1/1/2005

And in 2006 it would display

SUN/1/1/2006


PLEASE NOTE:

The FIXED NUMERICAL MONTH and DAY remain constant and€¦

Once I enter the FIXED NUMERICAL MONTH and DAY along with whatever
Formula€¦

I want the €śCURRENT YEAR€ť and the €śDAY OF THE WEEK€ť that the €śFIXED
MONTH and DAY€ť fall upon during the €śCURRENT YEAR€ť to ALL UPDATE
AUTOMATICALLY EVERY YEAR

THANKS FOR YOUR CONSIDERATION AND PLEASE RESPOND IF YOU CAN,

Thanks

Texcel

P.S. If you can do the above described€¦

Is there any way to add a €śFIXED TIME€ť to the Display e.g.
SAT/1/1/2005/6:00pm, SUN/1/1/2006/12:00AM


--
texcel
------------------------------------------------------------------------
texcel's Profile: http://www.excelforum.com/member.php...fo&userid=4325
View this thread: http://www.excelforum.com/showthread...hreadid=274211


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"