Date referencing

[Barry Clark]

Hello all. I am still trying to get this timesheet where I want it.

This is where I am at:

I have a cell that mentions the year (G7) and the sheet mentions the
pay period beginning (ex: January 1).

I would like to have a different cell (B9) combine the two into a date
that other cells would recognize as such.

For instance, if the sheet name is January 1st and cell G7 has "2006"
as a value, then I would like B9 to read "1/16" (year not shown). From
there another cell (A13) would show that day/date as "Sunday, January
01, 2006".

Any help would be greatly appreciated.

 

[Guest]

You might try using something like this:

If year is in cell A1 and month and day are in cell A2
=date(year(A1),month(A2),day(A2))
and format it appropriately.

 

[Barry Clark]

Thanks.

I tried that and it didn't work.

What I have is:

B7 = "January"
C7 = "1"
G7 = "2006"

Those three cells are formatted as Text.

B9 = "=DATE(YEAR(G7),MONTH(B7),DAY(C7))"

That cell is formatted as a date.

My return is "#value"

I have every add-in installed.

 

[Franz Verga]

Hello all. I am still trying to get this timesheet where I want it.

This is where I am at:

I have a cell that mentions the year (G7) and the sheet mentions the
pay period beginning (ex: January 1).

I would like to have a different cell (B9) combine the two into a date
that other cells would recognize as such.

For instance, if the sheet name is January 1st and cell G7 has "2006"
as a value, then I would like B9 to read "1/16" (year not shown). From
there another cell (A13) would show that day/date as "Sunday, January
01, 2006".

Any help would be greatly appreciated.

If your sheet is named "January 1st" (without quote) you can have this
formula in a cell, assume this cell would be E14, to extract the name of the
sheet:

=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))


then if in G7 you have the year, you can combine in a new cell ,say G9 to
have your starting date:


=VALUE("1/"&LEFT(E14,FIND(" ",E14)-1)&"/"&G7)

remember to format G9 as a date.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Barry Clark]

AWESOME, FRANZ!

Another question, your formula -
*"=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$
A$1))-FIND("]",CELL("filename",$A$1)))
"*- has the Month and the Day showing just as the sheet name does. I
have noticed that formatting the cell does not effect the way the
information in the cell is displayed. Why is that?

Thank you,

Barry

 

[Franz Verga]

AWESOME, FRANZ!

Another question, your formula -
*"=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$
A$1))-FIND("]",CELL("filename",$A$1)))
"*- has the Month and the Day showing just as the sheet name does. I
have noticed that formatting the cell does not effect the way the
information in the cell is displayed. Why is that?

Because it's text, not date...


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Bernard Liengme]

Hi Barry,
This UDF will give what you need in B9

Function MyDate()
Application.Volatile
MySheet = ActiveSheet.Name
MyMonth = Mid(MySheet, 1, 3)
Select Case MyMonth
Case "Jan"
Mynumber = 1
Case "Feb"
Mynumber = 2
Case "Mar"
Mynumber = 3
Case "Apr"
Mynumber = 4
Case "May"
Mynumber = 5
Case "Jun"
Mynumber = 6
Case "Jul"
Mynumber = 7
Case "Aug"
Mynumber = 8
Case "Sep"
Mynumber = 9
Case "Oct"
Mynumber = 10
Case "Nov"
Mynumber = 11
Case "Dec"
Mynumber = 12
End Select

MyStart = WorksheetFunction.Find(" ", MySheet) + 1
MyTest = (Mid(MySheet, MyStart, 1))
If IsNumeric(MyTest) Then
MyDay = MyTest
End If
MyTest = Mid(MySheet, MyStart, 2)
If IsNumeric(MyTest) Then
MyDay = MyTest
End If
MyDate = Mynumber & "/" & MyDay

End Function


And this will give what you need in A13
=DATE(G7,VALUE(MID(B9,1,FIND("/",B9)-1)),VALUE(MID(B9,1,FIND("/",B9)-1)))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Barry Clark" <[email protected]>
wrote in message
news:[email protected]...

 

[Barry Clark]

Can I convert it to a date?

AWESOME, FRANZ!

Another question, your formula -
*"=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$
A$1))-FIND("]",CELL("filename",$A$1)))
"*- has the Month and the Day showing just as the sheet name does. I
have noticed that formatting the cell does not effect the way the
information in the cell is displayed. Why is that?

Because it's text, not date...


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Barry Clark]

OK, I used DATEVALUE to convert it the text to a date. However, I would
really like a cell format that showed only the month values as January,
February and so on. I didn't see one in my list of options. Is there a
way that I can create a custom one?

Thank you,
Barry

 

[Barry Clark]

Nevermind, guys. I got it. Thanks.

 

[Barry Clark]

OK, here is another one.

I would like to combine the sheet name and a cell value to equal a ful
date. All in one cell.

I have the month and day part. That isn't a problem at all. For that,
used a modified version of Franz's formula
Code
-------------------
=DATEVALUE(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))
-------------------


This gives me whatever the sheet name is formatted as a date. However
I would also like it to reference a cell for the year value.

Basically, I would like it reference G7 for the year portion of th
date. I have tried a few things but have not been successful in
method. The last one I tried was to add
Code