CODEWITHSUNDEEP
excel
0x90
0x90

Reputation: 40982

How to input a date in custom format in Excel?

I have a cell with this value: Sat 24 Jan 2009 01:23:12 I defined a custom date format: jjj jj mmm aaaa hh:mm:ss

enter image description here

.but I can't do arithmetic on this date cell. For example I can't do "CELL + 1 hour"

Upvotes: 0

Views: 178

Answers (2)

Jerry
Jerry

Reputation: 71538

Excel doesn't exactly understand a text as a date when a custom formatting is created, so the way you described it in your question is not really going to work (unless someone actually knows a way to do that). I'd suggest using a helper formula that will work for the specific format that you have:

=SUBSTITUTE(SUBSTITUTE(MID(A1,5,20)," ","-",1)," ","-",1)*1

If you omit the *1 I have at the end, you should see the text I'm using before multiplying by 1 (that's where Excel attempts to convert the text to number, which is a date format that Excel can understand).

The above should give you a number with some decimals. Format it as date, with the formatting of your choice and you should be good to go. I used dd/mm/yyyy hh:mm:ss and got this:

enter image description here

The MID basically removes the weekday part, and the two SUBSTITUTEs replace the first 2 spaces with a dash each, and that's something Excel can understand as datetime.

Upvotes: 1

Lewis Williams
Lewis Williams

Reputation: 55

to add an hour to time just add +(1/24) to the formula or =cell+(1/24) in a new column

Upvotes: 1

Related Questions