હિમાંશુ પરભુભાઇ મિસ્ત્રી | Himanshu Mistry

જુલાઇ 30, 2008

How to lock a column/row/cell in MS Excel

Filed under: how to,Useful Links — Himanshubhai Mistry @ 9:31 પી એમ(pm)

How to Lock a column MS Excel

some time we need to lock a column from the end user protecting from accidental changes.

scenario:

   A           B 
1 data1      =fun(A1)
2 data2      =fun(A2)
3 data3      =fun(A3)

We want the user to allow key in data in column ‘A’
Function given in column ‘B’ will produce result and user will be able to view the result.
we don’t want the user to select/edit the cells of column ‘B’

steps how to achieve this:
[1] Select the columns (or rows/cells) the user should be able to to TYPE data into.
[2] Format menu / Cells / protection tab
[3] Remove the tick-mark from ‘locked’
[4] Tools menu / protection / protect sheet and apply protection

You will find that you can only type data in the cells that you selected and then removed the ‘locked’ tick-mark from.

This is applicable for cells, columns or rows..

Date Functions in MS Excel

Filed under: Useful Links — Himanshubhai Mistry @ 9:11 પી એમ(pm)

I found good information about Date related functions available in Microsoft Excel, from www.officearticles.com  website

Here it goes:

Today’s date and time:
=NOW()
Today’s date only:
=TODAY()

Day of the month for a specific date:
=DAY(TODAY())

Day of the week for a specific date (set the cell format to custom format “dddd”):
=WEEKDAY(TODAY())
=TEXT(WEEKDAY(TODAY()),”dddd”)

Month of the year for a specific date:
=MONTH(TODAY())

Year for a specific date:
=YEAR(TODAY())

Date for a specific year, month and day (Example returns 6/12/2005):
=DATE(2005,6,12)

Add days to a date (one week from today):
=TODAY()+7

Subtract days from a date (one week ago today):
=TODAY()-7

Last day of the this month:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
or
=EOMONTH(TODAY(),0)

Last workday of the current month:
=IF(WEEKDAY(EOMONTH(TODAY(),0))=6,EOMONTH(TODAY(),0)-1,IF(WEEKDAY(EOMONTH(TODAY(),0))=1,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))
or
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1,Holidays)

First workday of the following month:
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1,Holidays)

Number of workdays in the current month:
=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Holidays)

Last workday in range of days (A1 is start date, B1 is number of days in the period):
=WORKDAYS(A1,B1,Holidays)

DATEDIF() Syntax & Examples
The DATEDIF() function uses the following syntax:

=DATEDIF(start_date,end_date,”code”)

The start date must be less than the end date, or the function returns an error.

The following are the codes for the DATEDIF() function:

“y” Years
“m” Months
“d” Days
“md” Difference between days in a period; no month and years
“ym” Difference between the months in a period, no days and years
“yd” Difference between the days in a period, no years
Examples
To calculate the number of years between two dates:
=DATEDIF(A1,TODAY(),”y”) & ” Years”

To calculate the number of years and months between two dates:
=DATEDIF(A1,TODAY(),”y”) & ” Years, ” & DATEDIF(A1,TODAY(),”ym”) & ” Months”

To calculate the number of years, months, and days between two dates:
=DATEDIF(A1,TODAY(),”y”) & ” Years, ” & DATEDIF(A1,TODAY(),”ym”) & ” Months, ” & DATEDIF(A1,TODAY(),”md”) & ” Days”

Using the TEXT() function to calculate dates.
You can use the TEXT() to get the number of days or weeks between two dates:

Examples
Number of days:
=VALUE(TEXT((NOW()-$A$1)/24,”[h]”))

Number of weeks:
=VALUE(TEXT((NOW()-$A$1)/168,”[h]”))

source : http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_excel.htm

Create a free website or blog at WordPress.com.