[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

in excel how do I format the date like this 2011-04-21 06:00:00   for 50 hours from now ?

Posted on 2011-04-18
6
Medium Priority
?
202 Views
Last Modified: 2012-05-11
I want my spreadsheet to format the date like this 2011-04-21 06:00:00
and I want one cell to be 50 hours from now

Another cell 2 weeks & 6  hours ahead

excell 2007 :)

0
Comment
Question by:TIMFOX123
  • 4
6 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35420807
>>> I want my spreadsheet to format the date like this 2011-04-21 06:00:00

Format(Range("A1").Value,"YYYY-MM-DD HH:MM:SS")

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35420822
Oops and to Add time

=NOW()+"50:00:00"

and for

Another cell 2 weeks & 6  hours ahead

=NOW()+"174:00:00"

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35420828
If you are looking for a formula to format time then you can do this

=TEXT(A1,"YYYY-MM-DD HH:MM:SS")

or right click on the cell and click on format cells. The in Custom, Type

YYYY-MM-DD HH:MM:SS

Hope this helps.

Sid
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35420860
Attached is the example sheet.

BTW (Two weeks + 6 hrs) is

14 day * 24 hrs + 6 hrs = 342 hrs

So the formula should be =NOW()+"342:00:00"

I have also formatted the cell B3 and C3 to reflect "YYYY-MM-DD HH:MM:SS" by right clicking on the cell and then selecting format cells~~>Custom.

Hope this helps...


Sid
Book1.xls
0
 
LVL 1

Expert Comment

by:archonas
ID: 35420879
"Format Cells" for the date fields and in the Number tab select Custom from the Category field then enter the following in the "Type:" field

yyyy-mm-dd hh:mm:ss

When manipulating Excel Date fields mathematically you need to know that 1=24hrs. So to add 50hrs to a date field you have to divide it by 24.

The formula for the 50+ hour field (using A1 as the original date field)

=A1+50/24

For 2 weeks 6 hours

=A1+14.25
0
 

Author Closing Comment

by:TIMFOX123
ID: 35421257
this is a huge saver for a project I am doing
thx a bunch
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question