ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Convert YY[julian day] to dd-mmm-yy - Julian Date.xls (0/1) (https://www.excelbanter.com/excel-worksheet-functions/450863-convert-yy%5Bjulian-day%5D-dd-mmm-yy-julian-date-xls-0-1-a.html)

Phrank

Convert YY[julian day] to dd-mmm-yy - Julian Date.xls (0/1)
 
Hi,

I'm trying to convert a 5 digit serial number to the manufacture date.
The serial number is formatted as yy[julian day] (e.g., 12080, which
equates to a manufacture date of 20 March, 2012 (20-Mar-12).

We have the julian date table with the day of the month in column A
and the months in row one, and I inserted a row to convert the month
names to numbers, and all the julian days are in cells B3:M33, as
shown below. There is a leap year table and a non-leap year table,
but I won't have a problem with that part. My problem is gleaning the
date and month from the given julian day. I've tried index/match with
a day entered in cell O22, but I get the #N/A error. I figured if I
can at least get the Date in one formula and then the month in the
other, I can then concatenate those with the extracted yy from the
serial number. But I can't figure out why the index/match formula
isn't working. I've also attached my workbook if that helps. I would
appreciate any help and advice. Thanks!

Frank

=INDEX(A1:A33,MATCH(O22,A2:M33,0))

1 2 3 4 5 6 7 8 9
10 11 12
DATE JAN FEB MAR APR MAY JUN JUL AUG
SEP OCT NOV DEC
1 1 32 61 92 122 153 183 214
245 275 306 336
2 2 33 62 93 123 154 184 215
246 276 307 337
3 3 34 63 94 124 155 185 216
247 277 308 338
4 4 35 64 95 125 156 186 217
248 278 309 339
5 5 36 65 96 126 157 187 218
249 279 310 340
6 6 37 66 97 127 158 188 219
250 280 311 341
7 7 38 67 98 128 159 189 220
251 281 312 342
8 8 39 68 99 129 160 190 221
252 282 313 343
9 9 40 69 100 130 161 191 222
253 283 314 344
10 10 41 70 101 131 162 192 223
254 284 315 345
11 11 42 71 102 132 163 193 224
255 285 316 346
12 12 43 72 103 133 164 194 225
256 286 317 347
13 13 44 73 104 134 165 195 226
257 287 318 348
14 14 45 74 105 135 166 196 227
258 288 319 349
15 15 46 75 106 136 167 197 228
259 289 320 350
16 16 47 76 107 137 168 198 229
260 290 321 351
17 17 48 77 108 138 169 199 230
261 291 322 352
18 18 49 78 109 139 170 200 231
262 292 323 353
19 19 50 79 110 140 171 201 232
263 293 324 354
20 20 51 80 111 141 172 202 233
264 294 325 355
21 21 52 81 112 142 173 203 234
265 295 326 356
22 22 53 82 113 143 174 204 235
266 296 327 357
23 23 54 83 114 144 175 205 236
267 297 328 358
24 24 55 84 115 145 176 206 237
268 298 329 359
25 25 56 85 116 146 177 207 238
269 299 330 360
26 26 57 86 117 147 178 208 239
270 300 331 361
27 27 58 87 118 148 179 209 240
271 301 332 362
28 28 59 88 119 149 180 210 241
272 302 333 363
29 29 60 89 120 150 181 211 242
273 303 334 364
30 30 90 121 151 182 212 243
274 304 335 365
31 31 91 152 213 244
305 366




Don Guillett[_2_]

Convert YY[julian day] to dd-mmm-yy - Julian Date.xls (0/1)
 
On Saturday, May 9, 2015 at 7:27:19 AM UTC-5, Phrank wrote:
Hi,

I'm trying to convert a 5 digit serial number to the manufacture date.
The serial number is formatted as yy[julian day] (e.g., 12080, which
equates to a manufacture date of 20 March, 2012 (20-Mar-12).

We have the julian date table with the day of the month in column A
and the months in row one, and I inserted a row to convert the month
names to numbers, and all the julian days are in cells B3:M33, as
shown below. There is a leap year table and a non-leap year table,
but I won't have a problem with that part. My problem is gleaning the
date and month from the given julian day. I've tried index/match with
a day entered in cell O22, but I get the #N/A error. I figured if I
can at least get the Date in one formula and then the month in the
other, I can then concatenate those with the extracted yy from the
serial number. But I can't figure out why the index/match formula
isn't working. I've also attached my workbook if that helps. I would
appreciate any help and advice. Thanks!

Frank

=INDEX(A1:A33,MATCH(O22,A2:M33,0))

1 2 3 4 5 6 7 8 9
10 11 12
DATE JAN FEB MAR APR MAY JUN JUL AUG
SEP OCT NOV DEC
1 1 32 61 92 122 153 183 214
245 275 306 336
2 2 33 62 93 123 154 184 215
246 276 307 337
3 3 34 63 94 124 155 185 216
247 277 308 338
4 4 35 64 95 125 156 186 217
248 278 309 339
5 5 36 65 96 126 157 187 218
249 279 310 340
6 6 37 66 97 127 158 188 219
250 280 311 341
7 7 38 67 98 128 159 189 220
251 281 312 342
8 8 39 68 99 129 160 190 221
252 282 313 343
9 9 40 69 100 130 161 191 222
253 283 314 344
10 10 41 70 101 131 162 192 223
254 284 315 345
11 11 42 71 102 132 163 193 224
255 285 316 346
12 12 43 72 103 133 164 194 225
256 286 317 347
13 13 44 73 104 134 165 195 226
257 287 318 348
14 14 45 74 105 135 166 196 227
258 288 319 349
15 15 46 75 106 136 167 197 228
259 289 320 350
16 16 47 76 107 137 168 198 229
260 290 321 351
17 17 48 77 108 138 169 199 230
261 291 322 352
18 18 49 78 109 139 170 200 231
262 292 323 353
19 19 50 79 110 140 171 201 232
263 293 324 354
20 20 51 80 111 141 172 202 233
264 294 325 355
21 21 52 81 112 142 173 203 234
265 295 326 356
22 22 53 82 113 143 174 204 235
266 296 327 357
23 23 54 83 114 144 175 205 236
267 297 328 358
24 24 55 84 115 145 176 206 237
268 298 329 359
25 25 56 85 116 146 177 207 238
269 299 330 360
26 26 57 86 117 147 178 208 239
270 300 331 361
27 27 58 87 118 148 179 209 240
271 301 332 362
28 28 59 88 119 149 180 210 241
272 302 333 363
29 29 60 89 120 150 181 211 242
273 303 334 364
30 30 90 121 151 182 212 243
274 304 335 365
31 31 91 152 213 244
305 366


https://www.google.com/?gws_rd=ssl#q...+calendar+date

Phrank

Convert YY[julian day] to dd-mmm-yy - Julian Date.xls (0/1)
 
Looks like I was trying to make a mountain out of a molehill again. I
went to Chip Pearson's site (http://www.cpearson.com/excel/jdates.htm)
and his formula works like a charm. Thank you!

On Sat, 9 May 2015 06:21:23 -0700 (PDT), Don Guillett
wrote:

On Saturday, May 9, 2015 at 7:27:19 AM UTC-5, Phrank wrote:
Hi,

I'm trying to convert a 5 digit serial number to the manufacture date.
The serial number is formatted as yy[julian day] (e.g., 12080, which
equates to a manufacture date of 20 March, 2012 (20-Mar-12).

We have the julian date table with the day of the month in column A
and the months in row one, and I inserted a row to convert the month
names to numbers, and all the julian days are in cells B3:M33, as
shown below. There is a leap year table and a non-leap year table,
but I won't have a problem with that part. My problem is gleaning the
date and month from the given julian day. I've tried index/match with
a day entered in cell O22, but I get the #N/A error. I figured if I
can at least get the Date in one formula and then the month in the
other, I can then concatenate those with the extracted yy from the
serial number. But I can't figure out why the index/match formula
isn't working. I've also attached my workbook if that helps. I would
appreciate any help and advice. Thanks!

Frank

=INDEX(A1:A33,MATCH(O22,A2:M33,0))

1 2 3 4 5 6 7 8 9
10 11 12
DATE JAN FEB MAR APR MAY JUN JUL AUG
SEP OCT NOV DEC
1 1 32 61 92 122 153 183 214
245 275 306 336
2 2 33 62 93 123 154 184 215
246 276 307 337
3 3 34 63 94 124 155 185 216
247 277 308 338
4 4 35 64 95 125 156 186 217
248 278 309 339
5 5 36 65 96 126 157 187 218
249 279 310 340
6 6 37 66 97 127 158 188 219
250 280 311 341
7 7 38 67 98 128 159 189 220
251 281 312 342
8 8 39 68 99 129 160 190 221
252 282 313 343
9 9 40 69 100 130 161 191 222
253 283 314 344
10 10 41 70 101 131 162 192 223
254 284 315 345
11 11 42 71 102 132 163 193 224
255 285 316 346
12 12 43 72 103 133 164 194 225
256 286 317 347
13 13 44 73 104 134 165 195 226
257 287 318 348
14 14 45 74 105 135 166 196 227
258 288 319 349
15 15 46 75 106 136 167 197 228
259 289 320 350
16 16 47 76 107 137 168 198 229
260 290 321 351
17 17 48 77 108 138 169 199 230
261 291 322 352
18 18 49 78 109 139 170 200 231
262 292 323 353
19 19 50 79 110 140 171 201 232
263 293 324 354
20 20 51 80 111 141 172 202 233
264 294 325 355
21 21 52 81 112 142 173 203 234
265 295 326 356
22 22 53 82 113 143 174 204 235
266 296 327 357
23 23 54 83 114 144 175 205 236
267 297 328 358
24 24 55 84 115 145 176 206 237
268 298 329 359
25 25 56 85 116 146 177 207 238
269 299 330 360
26 26 57 86 117 147 178 208 239
270 300 331 361
27 27 58 87 118 148 179 209 240
271 301 332 362
28 28 59 88 119 149 180 210 241
272 302 333 363
29 29 60 89 120 150 181 211 242
273 303 334 364
30 30 90 121 151 182 212 243
274 304 335 365
31 31 91 152 213 244
305 366


https://www.google.com/?gws_rd=ssl#q...+calendar+date



All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com