Obtaining the sheet name within a cell for each sheet

[mrhicks]

Hello all,

I have an excel file that houses my time sheet for my employer. For
each week I have a separate work sheet ( 2004-08-27, 2004-08-20 etc...
). I am trying to automate calculating the hours for the whole day
etc... The last piece I am trying to do is have the dates on the
worksheet be based from the sheet name. So if the sheet name is
"2004-08-27" then my end day for the week is this day, then each
adjacent before it would just be - minus one. Right now I have
something like this to extract the sheet name that I found on
expert-exchange

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename"))-FIND("]",CELL("filename")))

This value is place in a cell, say A16 then for cells I need the
date I have

Cell H6 = VALUE(MID(A16,6,2)&"/"&RIGHT(A16,2)&"/"&MID(A16,3,2))

Then each adjacent cell before looks like

Cell G6 = H6-1
Cell F6 = H6-2

and so on.

This sheets to work, but not fully.. If I create another sheet for
the next week then change the sheet name to 2004-09-03 then hit enter
the dates on the new sheet reflect the correct date, but now the dates
on sheet 2004-08-27 now show dates ending on 09/03/04, which is not
correct. How can I correct this problem? Perhaps there is a better
solution that someone knows of? Any help is greatly appreciated.
Thanks

Mark

 

[JE McGimpsey]

If you don't include the reference argument, CELL() returns the sheet
name of the last calculated sheet.

This is a bit shorter, has fewer function calls, and includes the
reference argument:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

where 255 is just a large number.

You can also use

H6: =DATEVALUE(A16)

 

[mrhicks]

JE,

Thanks, this seems to work like a charm... Thank you very much..

Mark

If you don't include the reference argument, CELL() returns the sheet
name of the last calculated sheet.

This is a bit shorter, has fewer function calls, and includes the
reference argument:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

where 255 is just a large number.

You can also use

H6: =DATEVALUE(A16)

I have an excel file that houses my time sheet for my employer. For
each week I have a separate work sheet ( 2004-08-27, 2004-08-20 etc...
). I am trying to automate calculating the hours for the whole day
etc... The last piece I am trying to do is have the dates on the
worksheet be based from the sheet name. So if the sheet name is
"2004-08-27" then my end day for the week is this day, then each
adjacent before it would just be - minus one. Right now I have
something like this to extract the sheet name that I found on
expert-exchange

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename"))-FIN
D("]",CELL("filename")))

This value is place in a cell, say A16 then for cells I need the
date I have

Cell H6 = VALUE(MID(A16,6,2)&"/"&RIGHT(A16,2)&"/"&MID(A16,3,2))

Then each adjacent cell before looks like

Cell G6 = H6-1
Cell F6 = H6-2

and so on.

This sheets to work, but not fully.. If I create another sheet for
the next week then change the sheet name to 2004-09-03 then hit enter
the dates on the new sheet reflect the correct date, but now the dates
on sheet 2004-08-27 now show dates ending on 09/03/04, which is not
correct. How can I correct this problem? Perhaps there is a better
solution that someone knows of? Any help is greatly appreciated.