Free Microsoft Excel 2013 Quick Reference

# Date to Calendar

Hello.

I'm having a struggle to find a way to sort out my problem. If you could clarify I would be seriously appreciated.

I have an excel sheet where the whole column A and B are blank, and starting on column C and forwards I have a year calendar.

So far so good.

What I'm trying to find out is the right formula to:

write manually a date on one of the cells in Column A or B and the calendar automatically recognises that cell and highlights the date on the calendar.

I'm sorry if you don't understand I'm trying my best.

RLory

## Related Results

### Changing Column with Julian date to Calendar date

First off, I know little to nothing about Excel and how this can be done so I am asking for complete example and not just a snippet to work with. PLEASE

Here is my problem:

I have a column that contains a Julian date and I need to create a macro that can be run that will convert that date to a Calendar date. I need it as a macro because the Excel data is updated daily and this column always comes in as a Julian date.

I have found code that will convert the date to calendar date, but I don't know how to get it to work for the entire column or how to make it a macro to can be run as needed.

=DATE(2000+INT(L2/1000),1,MOD(L2/1000))
I need this to work for the entire L column

THANKS IN ADVANCE FOR ALL HELP

### How to Convert a Julian Date to Calendar Date

How do I Convert a Julian Date to Calendar Date?

Examples: 7365 = 12/31/07

The first number represents the Year and the next 3 numbers are the days of
that year. What formula would I type to get this. Also If I wanted to
subtract the julian date to find out (example when a product will expire)
(7365 - todays date - 180 days = ?) .........(The result would be desired in

### Convert listing of events with dates to calendar - Pls help

Is there a way to convert a table that lists events with dates to a calendar
format? For example, I have

Event Date
Seminar 4/15/2005
Posting 5/4/2005
End 7/20/2005

I would then have individual sheets for the months with the items above in
the appropriate boxes in the month calendar. Any help would be appreciated!

### Link Excel dates to Calendar in Outlook

Is there any way to utilize the Outlook Calendar/Tickler system for dates

Ideally, I would like to set up a Spreadsheet to trigger a reminder window
similar to the Outlook reminder system for dates I'm putting into
spreadsheets. Or even import the information attached to dates in the

Does anyone have any ideas for this?

### How to convert julian date to regular calendar date

I need help converting Julian Date to regular calendar date.
I have from 5000 â€“ 10000 Julian Dates in one column. Ex.
Col A need converted to
4001 = 1 Jan 04
5001 = 1 Jan 05
5030 = 30 Jan 05

Any help would be much appreciated.
Thanks
Ron

--
Ron

### Convert dates to calendar

Ok, here is what I need. I have a sheet (as attached), that outlines a schedule of payments from a customer. The dates are setup something like this:
Start ---------Finish ---Price Per Month
12/1/2008 - 12/13/2009_ \$100
12/14/2009 - 4/15/2010_ \$126
4/16/2010 - 5/1/2010_ \$0.00
5/2/2010 - 5/1/2014_ \$500

All of these numbers must have the ability to be modified.

What I need is to calculate how much to bill them each calendar month. Such as:

Novemer 2008--December 2008 ... December 2009--January 2009
---\$100------------\$100---------\$41.94+73.16------\$126.00

Note that, as long as it is a full month, they are charged the full month (ie a month with 28 days is not less expensive than one with 31). However, if the month is split between two payment schedules, it's days determine how much to charge from either plan. Also, the months in the calendar should not be calendar, but manually entered.

I greatly, greatly appreciate your help!!!

### Transfer dates to "calendar"

Hello everybody.

I'm new to the forums and I appreciate any imput.

I work in a travel agency, the way we keep track of how many people come and go is with a spreadsheet in which we have the client's number, arrival date, departure date, name etc. We manage around 4,000 people each year. What I want to do is find a way to transfer the duration of their trips to a calendar. I'm attaching pictures of both our main lista and the calendar I was thinking about.

Is there a formula or macro that can take those date values and transfer them to the other spread sheet? Or is there any other way in which I can know at any given time, how many people we have in house?

Thanks

Tunk

### Add Dates To TextBox From Second UserForm

Hi,

I have attached the sample workbook so that you can go through it.

Following are the purpose of the code I have written

1. Run User Form 'Down Time Calculation' when any cell in column number 20 is selected and 'F9' is pressed.

2. Above user form has four text boxes which will accept 'Date From' ,'Time From' , ' Date To and 'Time To' sequentially.

3. User can enter the date either by typing or by selecting the date from calendar that can be invoked by pressing 'Esc' key.

4. I have user form for calendar and I wish user can activate calendar only by pressing 'Esc' key when they are in 'Date From' or 'Date To' Text box of 'DownTime' Userform. But here I get stuck. I can activate calendar when I press 'Esc' key irresepective of cell selection . However I can not do it when I am in the 'Date From' text box of the 'Down Time Calculation' userform.

How do I activate the calendar userform being in first textbox of 'DownTime' userform and pressing 'Esc' Key

Thanks and Regards

Angshuman

### VBA: getting userforms to return date to other subs

Hi All,

I have a calendar widget that allows a user to choose a date thereby avoiding date format issues. I'm presently using it to set cell values and that works fine since the setting is done from inside the widget.

My problem is that I want to generalize my widget so that it returns a date instead of setting the value of a cell.

In other words:

sub doing_things_that_need_a_date ()
dim mydate as date
mydate= "return value of calendar widget"
'then go do stuff with mydate
end sub

Private Sub OKButton_Click()
mydate = Calendar1.value 'previously this line didn't include mydate and did set the value of the active cell
End Sub

I know that I have the code for the OKButton wrong, I just don't know what to put so that the widget will return a date to the sub from which it is called.

Any thoughts?

-Jen

### Block Out Dates For Calendar Vba

Hi guys, I'm new to the forum. I currently live in Anchorage, AK. I love messing around with excel but am still in the learning phase. I took this vba from the common questions messages. And I'm trying to tweak it to become my main calendar at work. I was wondering just 1 thing.

How do you black out the past dates?

Like today is september 14th, tomorrow I want september 15th to be highlighted but all dates previous to september 15th to be black.

Here is the code if it helps any one out. Thanks in advance for the help.
```
VB:
CreateCalendar()
Dim lMonth As Long
Dim strMonth As String
Dim rStart As Range
Dim rCell As Range
Dim lDays As Long
Dim dDate As Date

ActiveWindow.DisplayGridlines = False
With Cells
.ColumnWidth = 6#
.Font.Size = 8
End With

For lMonth = 1 To 4
Select Case lMonth
Case 1
strMonth = "January"
Set rStart = Range("A1")
Case 2
strMonth = "April"
Set rStart = Range("A8")
Case 3
strMonth = "July"
Set rStart = Range("A15")
Case 4
strMonth = "October"
Set rStart = Range("A22")
End Select

'Merge, AutoFill and align months
With rStart
.Value = strMonth
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 6
.Font.Bold = True
With .Range("A1:G1")
.Merge
.BorderAround LineStyle:=xlContinuous
End With
.Range("A1:G1").AutoFill Destination:=.Range("A1:U1")
End With
Next lMonth

'Pass ranges for months
For lMonth = 1 To 12
strAddress = Choose(lMonth, "A2:G7", "H2:N7", "O2:U7", _
"A9:G14", "H9:N14", "O9:U14", _
"A16:G21", "H16:N21", "O16:U21", _
"A23:G28", "H23:N28", "O23:U28")
lDays = 0
'Add dates to month range and format
lDays = lDays + 1
dDate = DateSerial(Year(Date), lMonth, lDays)
If Month(dDate) = lMonth Then ' It's a valid date
With rCell
.Value = dDate
.NumberFormat = "ddd dd"
End With
End If
Next rCell
Next lMonth

With Range("A1:U28")
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions(1).Interior.ColorIndex = 1
End With
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Returning Date from Calendar

Hi,

I am trying to return a date using Calendar Control.

I have Calendar1 within frmCalendar.

Code for Calendar1
Code:
```Private Sub Calendar1_Click()
Dim iDate
iDate = Calendar1.Value
End Sub```
I then have a command button located within worksheets to run another macro. The macro that it calls is call_data:
Code:
```Sub call_data()

Dim ws As Worksheet
Dim lRow, fRow, Lines As Long
Dim iDate As Date

Application.ScreenUpdating = False

Set ws = ActiveSheet
lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

cmdDatabaseOpen_Click

Lines = Cells(Rows.Count, 1).End(xlUp).Row - 1

Cells(2, 1).Resize(Lines, 2).Copy _
ws.Cells(lRow, 2)

ActiveWorkbook.Close

frmCalendar.Show

iDate = Calendar1.Value

fRow = lRow
lRow = fRow + Lines - 1

For i = fRow To lRow
On Error Resume Next
Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 8, 0)
Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 3, 0)
Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 10, 0)
Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 6, 0)
Cells(i, 9) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 13, 0)
Cells(i, 11) = iDate
Cells(i, 13) = Application.WorksheetFunction.SumIf(Range("I5:I" & i), Range("I" & i), Range("C5:C" & i))
With Cells(i, 15)
.Formula = "=SUMPRODUCT(--(M" & i & ">" & ws.Name & "_Bands),(M" & i & "-" & ws.Name & "_Bands)," & ws.Name &
"_Diff)"
.Value = .Value
End With
Cells(i, 14) = Cells(i, 15) - Application.WorksheetFunction.SumIf(Range("I5:I" & i - 1), Range("I" & i), Range("N5:N"
& i - 1))
Next

Application.ScreenUpdating = True

End Sub```
How do I link iDate in Calendar1 to iDate in call_data?

Best regards
Jon

### Link or Copy Excel Data to Calendar (for scheduling) for Visual Aid

Hola,

I am working on a project that entails scheduling preventive
maintenance, such as oil changes and inspections, on trucks. I have an
excel spreadsheet data which contains the dates as to when and what type
of preventive maintenance should be scheduled. Now I need to import
that data to calendar (maybe outlook) and then be able to print it for
employees.

Please let me know if this is possible in excel or maybe possible in
access. Thank you so much.

Heng

--
supafreeza
------------------------------------------------------------------------
supafreeza's Profile: http://www.excelforum.com/member.php...o&userid=24774

### Picking a Date to Populate a Cell

Please go easy on me because I'm brand new to Excel. I am trying to find a
way to 'select' a date to populate a cell rather than having to 'type' it in.
Is there a way I can use the Data Validation drop-down function to call up
an interactive calendar from which I can pick a date? If not, is there
another way it can be done?

### Distributing Rents to Calendar Year

Gentlemen,

I have a challenging problem. I am preparing a leasing model for tenants in a property. The lease contracts are in rental years and, what I am trying to do is to distribute those rental year figures to calendar year to see an annual income. However the formula I wrote has some flaws.

I have identified the problems with different types of tenants in the file.

PS: For those not familiar with real estate rental year contract, means tenant enters the property at a certain date and pays the same rent until its entry date next year. An example:

Tenant A will pay escalating rents of 5, 10, 15 in its first 3 rental years. Lets assume entry Date is June 20, 2008. Tenant pays 5 until June 20, 2009, 10 Between June 20, 2009 and June 20 2010 and so on.

### How do I get a date to pop up in another sheet

I am trying to link one sheet with a randomly selected date, to pop up on another sheet, (calendar) and turn a selected color. I'm trying to build something to keep track of times taken for time off from work.

Is this possible?

### Link or Copy Excel Data to Calendar (for scheduling) for Visual Aid

Hola,

I am working on a project that entails scheduling preventive maintenance, such as oil changes and inspections, on trucks. I have an excel spreadsheet data which contains the dates as to when and what type of preventive maintenance should be scheduled. Now I need to import that data to calendar (maybe outlook) and then be able to print it for employees.

Please let me know if this is possible in excel or maybe possible in access. Thank you so much.

Heng

### Converting US date to the rest of the world in Excel

Hi

I just wanted to thank rebsbails for posting a working solution to my problem and saving me a lot of time, and no thanks to power crazed mods. Why do mods on all forums complain when you ask a question that has already been asked, and yet lock threads when someone posts such a relevant post to old threads that google has already tracked? I never understand

To convert backwards-US date to the rest of the world:

=CONCATENATE((IF(MID(B11,4,1)="/",MID(B11,3,1),IF(MID(B11,2,1)="/",MID(B11,3,2),IF(MID(B11,6,1)="/",MID(B11,4,2),MID(B11,4,1))))),"/",IFERROR((IF(SEARCH("/",LEFT(B11,2)),LEFT(B11,1),LEFT(B11,2))),LEFT(B11,2)),"/",RIGHT(B11,4))

Thanks rebsbails cheers

### Convert date to australian format

Hi all. I currently import a txt file which is extracted from a seperate system into excel. Most of the dates come through in the australian format(dd/mm/yyyy column d), but for some it converts to mm/dd/yyyy. Not sure why it does this as the system shows the date in the correct format.

Using conditional format (column g) I am trying to show all orders open >0, >30 and >60 days using
```
VB:
today()-d#

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
. However, the dates converted to mm/dd/yyyy are all showing up as a negative number. I have tried using left, right and mid to extract these dates to convert to the correct format (dd/mm/yyyy).

I have a seperate column (i) which subtracts the date in d from the current date as i want to then use sumproduct in a summary field.

### Increment Date To Get The Next Non-Holiday

Hi all, I have a Date database(Sheet1) and holiday Database(Sheet2). In cell D2, i have a date(Eg, 22/3/11). I want the date to automatically add itself if it is a saturday or sunday and if any of these date fall on the holidays using VBA macro.

Is it Possible??

### Compare dates to bring a percentage

Hi Guys

Basically I have a spreadsheet which has lots of features which have a timeline against them.
I want to compare the Actual Date to the Planned Date to show either On time Early Late.
But I want an overall percentage of how many are late, early, on time rather then by each feature.

### Convert Date to text

simply -- as example
I want to writ dopwn the Date as 12/12/10---- to be converted to text "Twilves of December two thounsnd and ten --
the point is how I can convert the Date to text as shown above?!!
is that VBA Code or just formula -in XLS ?!!

### Add future date to cell

I want to have a future date (30 days from today’s date) added to a cell in column A only if that cell is already blank and column N has an “O” in that same row. Here is the code that I have right now, and it works to add a value based on the conditions that I just described. I just don’t know how to get it to add a date to that is 30 days in advance of today’s date. Thanks for any help!

```
VB:

Me.Cells(.Row, "A").Value = "1"
End If

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Filtering data between dates to give a summary page indicated in months

Hi
I have a spreadsheet with 15 worksheets.
Each worksheet would be for a vehicle with entries of dates, driver, Congestion charge zone, speed fine, and bus path fine, parking fine amount and Date Paid.
These entries would be entered by hand.
Now I have a Summary page at the start with, Columns of each months date, and rows of the Vehicles linked to the worksheet.
Now I want to create a formula that will apply a conditional format to the month that the driver made one of the 4 mistakes. Thus if in the worksheet of the driver the entree is made for 12/03/05 and there is an entry under speed fine. It must change the cell under the column of March to example green that would indicate a speed fine.
So according to the date the entry is made the summary page on the first worksheet must change. I have made the sheets and all and changed the dates to numbers, and tried to sort it, through elimination, but am struggling.
Any help would be appreciated.
Kind regards
E

### Converting date to number using excel

Hi there,

I'm just new at learning Excel and Access and i need some information on how to convert date to number in Excel and also how to set and apply a filter that shows only certain records (eg.all dates before 31/1/04) which is in Access.
That would be great if anyone can help :